topic Re: Set Analysis in QlikView

Set Analysis

Wed, 30 Oct 2013 10:52:01 GMT

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

Re: Set Analysis

tresB — Wed, 30 Oct 2013 11:03:26 GMT

PFA.

Re: Set Analysis

Gysbert_Wassenaar — Wed, 30 Oct 2013 11:03:58 GMT

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

Re: Set Analysis

israrkhan — Wed, 30 Oct 2013 11:13:01 GMT

hi,

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

it might help..

Re: Set Analysis

ali_hijazi — Wed, 30 Oct 2013 11:15:17 GMT

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

Re: Set Analysis

Wed, 30 Oct 2013 12:56:02 GMT

Thanks ali,

But its not working.

my expected result is

Count of ID

Error 5 2

Error 10 1

Regards,

Sudha Banakar

Re: Re: Set Analysis

Gysbert_Wassenaar — Wed, 30 Oct 2013 13:10:44 GMT

See attached qvw

Re: Re: Set Analysis

andrewpettit — Wed, 30 Oct 2013 14:07:50 GMT

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

;

Left Join (Data)

LOAD Distinct

ID,

[Received Date],

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

Resident

Data

;

Drop Field MaxDate;