topic Re: FirstSortedValue( Aggr(Concat(distinct Name, ',') , Date), -Aggr(Date,Date)) this expression how to write in script level in QlikView

FirstSortedValue( Aggr(Concat(distinct Name, ',') , Date), -Aggr(Date,Date)) this expression how to write in script level

Anonymous — Sun, 10 Sep 2017 10:44:08 GMT

FirstSortedValue( Aggr(Concat(distinct Name, ',') , Date), -Aggr(Date,Date)) this expression how to write in script level

Re: FirstSortedValue( Aggr(Concat(distinct Name, ',') , Date), -Aggr(Date,Date)) this expression how to write in script level

vishsaggi — Sun, 10 Sep 2017 18:03:11 GMT

Can you provide some sample data with an expected output?

Re: FirstSortedValue( Aggr(Concat(distinct Name, ',') , Date), -Aggr(Date,Date)) this expression how to write in script level

surendraj — Sun, 10 Sep 2017 19:27:24 GMT

I think You want Name which belongs to highest date among all the listed dates.!!

Am I Correct?

Re: FirstSortedValue( Aggr(Concat(distinct Name, ',') , Date), -Aggr(Date,Date)) this expression how to write in script level

sunny_talwar — Sun, 10 Sep 2017 23:27:34 GMT

May be like this...

Assuming you have a table like this.....

Fact:

LOAD Name,

Date

FROM ....

AggrTable:

LOAD Concat(DISTINCT Name) as ConcatName,

Date

Resident Fact

Group By Date;

Right Join (AggrTable)

LOAD Max(Date) as Date

Resident AggrTable;

Re: FirstSortedValue( Aggr(Concat(distinct Name, ',') , Date), -Aggr(Date,Date)) this expression how to write in script level

sandeep_sarawgi — Fri, 29 Sep 2017 09:21:56 GMT

Hi Ashok,

Suggesting an alternative approach, in case if it still helps and to share with community a solution that worked for me and allowed me to avoid the complexity of writing a longer set-analysis expression:

Temp:
Load
distinct max(Date) as Date
Resident Facts
Group by Name;

Left Join (Facts)
Load
Date,
1 as MaxNameDate
Resident Temp;

Drop Table Temp;

Then use something like =Sum({<MaxNameDate={1}>}Value), as an example, for doing aggregation for records where date is max for name in chart with dimension = Name

Re: FirstSortedValue( Aggr(Concat(distinct Name, ',') , Date), -Aggr(Date,Date)) this expression how to write in script level

Kushal_Chawda — Fri, 29 Sep 2017 10:03:01 GMT

what is the reason to do this in script? Is there any performnace issue? If you can tell us your need ,we can suggest some easier expression with bit of script solution.

Perhaps, you can do like below

Fact:

LOAD Name,

Date

FROM Tbale;

left join(Fact)

LOAD concate(distinct Name,',') as Name,

Date

Resident Fact

group by Date;

left joion(Fact)

LOAD max(Date) as Date

1 as isMaxDate

resident Fact;

you can now use isMaxDate flag in your expression