Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

ashok25006323
New Contributor III

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

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

5 Replies
vishsaggi
Esteemed Contributor III

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

Can you provide some sample data with an expected output?

surendraj
Valued Contributor

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

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

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;

Highlighted
Partner
Partner

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

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

Sandeep Sarawgi

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

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