topic Re: Loading max date entry? in QlikView

Loading max date entry?

Tue, 04 Feb 2014 11:50:37 GMT

I am having a problem where I am trying to load monthly customer files and customers appear in more than one monthly file but sometimes with updated names etc. So I am trying to load the Customer ID and the Customer Names but only from the file with the latest date that each customer appears in. I have got it to the point where I have a date and I am using a peek function I am just not sure how to use the date in the peek function so that it gives me what I want. So in the excel block below I only want to load the last line:

CustomerID	Customer Name	FileDates
100535	Store A	01.01.2013
100535	Store A	01.02.2013
100535	Store A	01.03.2013
100535	Store A	01.04.2013
100535	Store A	01.05.2013
100535	Store A	01.06.2013
100535	Store A	01.07.2013
100535	Store A	01.08.2013
100535	Store A	01.09.2013
100535	Store A	01.10.2013
100535	Store A	01.11.2013
100535	Store B	01.12.2013

I am using the following script but it is not working 100% - does anyone have any suggestions?

LOAD FileDates,

CustomerID,

if(peek(CustomerID)=CustomerID and FileDates < peek(FileDates),Null(),[Customer Name]) as [Customer Name]

RESIDENT Customers

order by CustomerID, FileDates ASC;

Thanks

Re: Loading max date entry?

Peter_Cammaert — Tue, 04 Feb 2014 12:01:29 GMT

Use FirstsortedValue() to get the most recent Customer Name. You can use something like:

LOAD CustomerID, FirstSortedValue(CustomerName, -num(FileDates)) AS CustomerName

RESIDENT CUSTOMERS

GROUP BY CustomerID;

Make sure that FileDates contains a QlikView date field (e.g. string & numerical representation).

Peter

Re: Loading max date entry?

Tue, 04 Feb 2014 12:07:22 GMT

PFA

Re: Loading max date entry?

Tue, 04 Feb 2014 12:10:38 GMT

Thanks Peter that works perfectly!

Re: Loading max date entry?

Peter_Cammaert — Tue, 04 Feb 2014 12:16:49 GMT

FirstSortedValue() is a great function for another problem. Imagine that for every Customer ID you want to list the last CustomerName AND some details that belong to the same specific record. A GROUP BY doesn't allow you to grab details that aren't aggregated or present in the GROUP BY clause.

FirstSortedValue() fixes that because you can get the details from that same record (with the most recent name) by repeating the FirstSortedValue() expression on additional fields.

I'm sure you'll encounter a situation like that sooner or later.

Peter