Issue in Aggr and Set

[Anonymous]

Hi All.

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

AGGR and set behaving abruptly.

Regards

erika

[Gysbert_Wassenaar]

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

See attached qvw

[Anonymous]

Hi Gysbert,

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

Regards

Erika !

[Gysbert_Wassenaar]

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.

[Anonymous]

I want him to return any destination.

[Gysbert_Wassenaar]

'Any' is not an option Qlikview knows.

[its_anandrjs]

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

[Anonymous]

I want to return the value which has smallest word.

Eg: If its Shimla and Goa then return Goa

[Anonymous]

I want to do this at front end.

[its_anandrjs]

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