Discussion board where members can get started with QlikView.
I am creating a Qlikview dashboard, for which only 1 Excel table has been loaded. The table looks something like this
Now I'd like to design a table in Qlikview that only shows the values for each Customer ID with the last Reporting Date. So it should look like this:
How can I achieve this?
Use Customer ID as dimension and then expressions like
=FirstSortedValue(Status, -SubField([Reporting Date],' ',-1))
=FirstSortedValue([Reporting Date], -SubField([Reporting Date],' ',-1))
You can also create a field from Reporting Date in your script, using
Subfield([Reporting Date],' ',-1) as WeekNo ,
Then you can just use WeekNo as sort weight in FirstSortedValue (or try to interprete the Reporting Date as Date using MakeWeekDate().
Hope this helps,
the 2 expressions are
subfield(concat(Status, '@', aggr(MaxString([Reporting Date]),[Customer ID])),'@',1)
Here is script solution
Data: LOAD [Reporting Date], [Customer ID], Status FROM [https://community.qlik.com/thread/210992] (html, codepage is 1252, embedded labels, table is @1) where not wildmatch([Reporting Date] ,'*.*') ; New: NoConcatenate LOAD *, if([Customer ID]<> Peek([Customer ID],(RowNo()-1)-1),1,0) as Flag Resident Data Order by [Customer ID],[Reporting Date] desc; DROP Table Data;