Set Analysis

[#1]

Hi Everyone,

i have table like this.

ID      Received Date   Error Message

1        1/10/13               Error 1

1        2/10/13               Error 2

1        2/10/13               Error 3

1        4/10/13               Error 5----->Latest entry

2        1/10/13               Error 10------>Latest entry

3        10/10/13             Error 1

3         15/10/13            Error 5----->Latest entry

I want to count the ID  based on the latest entry of the error message.

How to do this in set analysis.

Please help me on this.

Thanks in advance,

Regards,

Sudha Banakar

[tresesco]

PFA.

[Gysbert_Wassenaar]

I don't understand what you want to count. You can count id's with count(distinct ID).

[israrkhan]

hi,

add take ID in Dimension and use expression like =FirstSortedValue([Error Message],-Received_Date)

it might help..

[ali_hijazi]

To get the latest Error entry next to each ID then you can use the FirstSortedValue function in your case it would be something like this: Aggr(FirstSortedValue(Error,-Received)ID) -Received just to translate the FirstSortedValue into something like LastSortedValue

[#6]

Thanks ali,

But its not working.

my expected result is

                                Count of ID

Error 5                           2

Error 10                          1

Regards,

Sudha Banakar

[Gysbert_Wassenaar]

See attached qvw

[andrewpettit]

If you try something like this on the backend you can then just sum the Latest Error Flag column by the Error Message to get the desired result.

Data:

LOAD * INLINE [

    ID, Received Date, Error Message

    1, 10/1/2013, Error 1

    1, 10/2/2013, Error 2

    1, 10/2/2013, Error 3

    1, 10/4/2013, Error 5

    2, 10/1/2013, Error 10

    3, 10/10/2013, Error 1

    3, 10/15/2013, Error 5

];

Left Join (Data)

LOAD Distinct

  ID,

  Max([Received Date]) AS MaxDate

Resident

  Data

Group By

  ID

;

Left Join (Data)

LOAD Distinct

  ID,

  [Received Date],

  If([Received Date] = MaxDate,1,0) AS [Latest Error Flag]

Resident

  Data

;

Drop Field MaxDate;