Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Find last occurance

...Hi,

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

Reporting DateCustomer IDStatusXXXYYYZZZ
week 6111Preparing proposal
week 6444Preparing contract
Week 7222Preparing contract
week 7333Preparing contract
week 7111Proposal sent
week 8444Contract 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 DateCustomer IDStatusXXXYYYZZZ
week 7111Proposal sent
week 7222Preparing contract
week 7333Preparing contract
week 8444Contract signed
...

How can I achieve this?

3 Replies
Highlighted
MVP
MVP

Re: Find last occurance

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
111Proposal sentweek 7
222Preparing contractWeek 7
333Preparing contractweek 7
444Contract signedweek 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

Highlighted
MVP
MVP

Re: Find last occurance

the 2 expressions are

MaxString([Reporting Date])

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

1.png

Highlighted
MVP
MVP

Re: Find last occurance

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)