topic Re: Find last occurance in QlikView

Find last occurance

Wed, 25 Nov 2020 16:16:04 GMT

...Hi,

I am creating a Qlikview dashboard, for which only 1 Excel table has been loaded. The table looks something like this

Reporting Date	Customer ID	Status
week 6	111	Preparing proposal
week 6	444	Preparing contract
Week 7	222	Preparing contract
week 7	333	Preparing contract
week 7	111	Proposal sent
week 8	444	Contract signed
....

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:

Reporting Date	Customer ID	Status
week 7	111	Proposal sent
week 7	222	Preparing contract
week 7	333	Preparing contract
week 8	444	Contract signed
...

How can I achieve this?

Re: Find last occurance

swuehl — Sat, 26 Mar 2016 22:09:29 GMT

Use Customer ID as dimension and then expressions like

=FirstSortedValue(Status, -SubField([Reporting Date],' ',-1))

=FirstSortedValue([Reporting Date], -SubField([Reporting Date],' ',-1))

Customer ID	=FirstSortedValue(Status, -SubField([Reporting Date],' ',-1))	=FirstSortedValue([Reporting Date], -SubField([Reporting Date],' ',-1))
	Contract signed	week 8
111	Proposal sent	week 7
222	Preparing contract	Week 7
333	Preparing contract	week 7
444	Contract signed	week 8

edit:

You can also create a field from Reporting Date in your script, using

LOAD

...

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,

Stefan

Re: Find last occurance

maxgro — Sat, 26 Mar 2016 22:31:19 GMT

the 2 expressions are

MaxString([Reporting Date])

subfield(concat(Status, '@', aggr(MaxString([Reporting Date]),[Customer ID])),'@',1)

Re: Find last occurance

Kushal_Chawda — Sun, 27 Mar 2016 05:23:10 GMT

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;

Expressions:

=Only({<Flag={1}>}[Reporting Date])

=Only({<Flag={1}>}Status)