topic Re: Re: Issue in Aggr and Set in QlikView

Issue in Aggr and Set

Anonymous — Tue, 24 Jun 2014 10:55:30 GMT

Hi All.

I want to find the Destination on Max date for every Name .

AGGR and set behaving abruptly.

Regards

erika

Re: Issue in Aggr and Set

Gysbert_Wassenaar — Tue, 24 Jun 2014 10:57:48 GMT

Use the firstsortedvalue function: FirstSortedValue(Dest,-Date)

See attached qvw

Re: Re: Issue in Aggr and Set

Anonymous — Tue, 24 Jun 2014 11:20:46 GMT

Hi Gysbert,

If Dates are same then also I want some Destination to be returned.

Regards

Erika !

Re: Re: Issue in Aggr and Set

Gysbert_Wassenaar — Tue, 24 Jun 2014 11:22:03 GMT

Which one? If there are several Qlikview cannot know which one should be returned. You need to define extra logic so Qlikview can determine what the result should be.

Re: Re: Issue in Aggr and Set

Anonymous — Tue, 24 Jun 2014 11:23:23 GMT

I want him to return any destination.

Re: Re: Issue in Aggr and Set

Gysbert_Wassenaar — Tue, 24 Jun 2014 11:32:07 GMT

'Any' is not an option Qlikview knows.

Re: Re: Issue in Aggr and Set

its_anandrjs — Tue, 24 Jun 2014 11:36:49 GMT

Hi Erika,

You can try this way also

LOAD Date,

num(Date) as NUM,

Month(Date) as Month,

Dest,

Name

FROM

(ooxml, embedded labels, table is Sheet1);

Left Join

LOAD

Name,

Date(Max(Date)) as Maxdate

Resident Sheet1

Group By Name;

NoConcatenate

Final:

LOAD

Name,

if(Date = Maxdate, Dest) as DesinationVisit,

if(Date = Maxdate, Name) as PersonVisit,

Maxdate as DateVisit

Resident Sheet1 Where Len(if(Date = Maxdate, Name)) > 0;

//DROP Table Sheet1;

And on straight chart use fields for chart display.

PersonVisit, DesinationVisit, DateVisit

Re: Re: Issue in Aggr and Set

Anonymous — Tue, 24 Jun 2014 11:37:44 GMT

I want to return the value which has smallest word.

Eg: If its Shimla and Goa then return Goa

Re: Re: Issue in Aggr and Set

Anonymous — Tue, 24 Jun 2014 11:38:40 GMT

I want to do this at front end.

Re: Re: Issue in Aggr and Set

its_anandrjs — Tue, 24 Jun 2014 11:50:46 GMT

The use FirstSortedValue(Dest,-Date) as Gysbert suggested and what is your expected output let me then it will be helpful.

Re: Re: Issue in Aggr and Set

Gysbert_Wassenaar — Tue, 24 Jun 2014 11:58:22 GMT

Try MaxString(aggr(if(NUM=max(total <Name> NUM),Dest),NUM,Dest,Name))

Re: Re: Issue in Aggr and Set

Anonymous — Tue, 24 Jun 2014 13:05:07 GMT

Thanks Gysbert!!!!!!

Re: Issue in Aggr and Set

rajni_batra — Mon, 30 Jun 2014 14:16:51 GMT

HI Gysbert ,

We tried this expresion but this is taking lot of Time and RAM on actual data to process..

Is there any other to optimize it or other suggestion!!!

Any help would be appreciated..

Regards,

rajni batra

Re: Issue in Aggr and Set

Gysbert_Wassenaar — Tue, 01 Jul 2014 12:39:43 GMT

Do the calculations in the script:

T1:

LOAD Date,

num(Date) as NUM,

Month(Date) as Month,

Dest,

Name

FROM ...etc

join load Name, max(NUM) as NUM, 1 as Flag

Resident T1

group by Name

Then use the new Flag field in the expression:

concat({<Flag={1}>} Dest, ', ')

Re: Re: Issue in Aggr and Set

rajni_batra — Tue, 01 Jul 2014 12:53:06 GMT

Hi Gysbert,

Thanks for your reply, Can't do it in scripting as max value is based on User selection,

I have tried something(basically trying to remove IF) which is working fine in some conditions..

like it is not working for current selection i have made but if i will clear that it gives fine output,

Please check if you could help in that...

Regards,

Rajni