3 Replies Latest reply: Aug 30, 2016 3:57 AM by Ankur Saraswat RSS

    Aggregate a table column by last value

    mdecau

      Good morning,

       

      I have a table that record the trades for each client, with a Tier associated to a client. It looks like that:

       

      Trade number

      Client NameClient Tier
      1John1
      2Jack3
      3William2
      4John2

      5

      Jack3
      6John2

       

      As you can see from row 1 to 4, a Client can have its Tier updated (here John is downgraded from 1 to 2).

       

      I have a table in a datawatch workbook which aggregates this table by Client Name. I would like to only keep the last state of Client Tier in this table. Here, John would have a Tier 2 (since trade number is in chronological order).

       

      Unfortunately, there is no aggregating option such as "Last" in the Visual Member tab.

       

      Is there any workaround to achieve this "last value aggregation"?

       

      Thank you for your help

        • Re: Aggregate a table column by last value
          DataExploiter _

          Marin Use the summary tab. In the formula tab use the formula max([Client Tier])

          • Re: Aggregate a table column by last value
            Ankur Saraswat

            Hi Marin,

             

            Is your data has any associated date/time value column?

            Assuming that an individual Client Name could not have two different Client Tier values (say John Tier is 1 as well as 2) at a particular time point.

            And considering that the Client Tier is getting updated repeatedly, i believe there should be a time value (for e.g. daily, hourly, etc) when a Client Tier value is getting changed.

             

            Using this date/time column we can display the last state of Client Tier for the complete time range, selected Time slice as well as for a particular time point, in the viz.

             

            To achieve this, simply select the 'Transform your data to enable Timeseries analysis' checkbox in the Designer data table.

            Add the Client Name column to the Breakdown and the Client Tier column to the Visual Members variable of a Table viz.

            Now, the table viz should be displaying the last state of Client Tier for the complete time range.

            Add a Time Filter to the dashboard,  to view the last state of Client Tier for a particular time slice.

            Use the Snapshot handle on the Time Filter, to view the last state of Client Tier at a particular time point.

             

            Hope you find this helpful.

             

             

            Thanks,

            Ankur