topic Re: Use of only function with 'Max' in set expression in QlikView

Use of only function with 'Max' in set expression

Tue, 06 Jan 2015 09:06:38 GMT

I have the following data tables:

Data:

LOAD * INLINE [

EventID, Status, Value

52, A, 10

53, E, 20

54, A, 30

];

Temp:

LOAD * INLINE [

ID, EventID

37, 52

37, 53

41, 54

41, 55

41, 56

41, 57

];

Requirement:

In straight table I have to display two columns 'ID' and 'Status'. There should be single row for each ID; meaning here, there should be distinct ID's in straight table. If there are multiple values for same ID, then it's 'Status' value in our straight table column should be of max of 'EventID'.

My Expression in straight table is :

if(count(DISTINCT [Status])>1,Only({<[EventID]={$(=max([EventID]))}>}[Status]),[Status])

For example:

The output I'm expecting in Straight table is :

ID Status

37 E

41 A

But its giving below output:

ID Status

37 -

41 A

'E' is expected in status column instead of null i.e.' - '.

I'm not getting the correct output. I used 'Aggr' function with max, but did'nt work. Also to mention, use of maxstring function is not expected here. I want help for this issue.

Can you suggest expression similar to this.

Re: Use of only function with 'Max' in set expression

jagan — Tue, 06 Jan 2015 09:20:43 GMT

Hi,

Why there is no status for 55, 56 and 57? if there are values for that then use

=FirstSortedValue(Status, -EventID)

Regards,

Jagan.

Re: Use of only function with 'Max' in set expression

tresB — Tue, 06 Jan 2015 09:30:49 GMT

And if you have to take nulls into consideration and avoid it, try like:

FirstSortedValue({<Status={'*'}>}Status,-EventID)

PFA

Re: Use of only function with 'Max' in set expression

Tue, 06 Jan 2015 10:23:52 GMT

Hi All,

The expression suggested by you is not working for me.

For ID '37', its still displaying '-' instead of 'E' value in 'Status' column. !

Re: Use of only function with 'Max' in set expression

tresB — Tue, 06 Jan 2015 10:35:23 GMT

A distinct would be required. Try like:

=FirstSortedValue({<[Patient Status]={'*'}>} Distinct [Patient Status], -[SS_Event_EP_ID])

Re: Use of only function with 'Max' in set expression

Tue, 06 Jan 2015 11:00:51 GMT

Thanks, It worked.

I appreciate your help!!