topic Problem counting the last modified data only in QlikView

Problem counting the last modified data only

Tue, 15 Mar 2011 15:29:36 GMT

I'm trying to count number of HouseNumber's online at a certain date. However due to the fact that my data changes over time for a specific HouseNumber I only want to look at the last modified value.


Content_tmp:
LOAD ContentAccountId as EntriqId,
ItemId,
if(right(ItemId,4)<>'_trl', left(ItemId, 6)) as HouseNumber,
//if(right(ItemId,4) = '_trl', 'True', 'False') as Trailer,
MediaType,
Affiliate,
if(Country='SE', 'Sweden', if(Country='NO', 'Norway', if(Country='FI', 'Finland', if(Country='DK', 'Denmark', 'Unknown')))) as Country,
ReleaseDate,
ReleaseEnd,
date(Date#(Left(ModifyDate, 10), 'YYYY-MM-DD')) as ModifyDate
FROM $(DataPath)ReleaseDateIncremental_$(Date_test)*.csv (ansi, txt, delimiter is ',', embedded labels, msq);

And my chart property expression look something like this for Sweden,

Count(Distinct If(Country='Sweden' AND OrderDate>=ReleaseDateStart AND OrderDate<=ReleaseDateEnd, HouseNumber))

OrderDate is the date I choose and want to know the amount of HouseNumbers.

I'm running Qlikview 8.5 at the moment.

Problem counting the last modified data only

matt_crowther — Tue, 15 Mar 2011 16:36:32 GMT

Hopefully this will provide you with a starting point:

I'm doing something similar with Insurance policies that change over time but this should work with any dimension value that has multiple date / time-stamped entries against it.

When you load you're data (or via a resident load if from a qvd) order it in the following way along with the shown preceding load statement to derive a 'CurrentRecordInd' (or 'Last_Modified_Record_Ind' in your case):

Data:

Load *,

If(previous(HouseNumber)=HouseNumber,0,1) as Last_Modified_Record_Ind;

Load *;

From XYZ.xls Order By HouseNumber Asc, Date Desc;

(Script isn't checked)

Essentially what we're doing is grouping all the entries that relate to a particular house together and then sorting them in descending order to place the most recent at the top within the house number grouping. Using the 'Previous' statement allows us to check if the line we're looking at is a new house number - if it is it must be the most recent as they're sorted in descending order. Conversely if the previous House Number is the same as the current record then it must be a more recent entry and therefore should return '0'.

It's a simple principal that I've probably explained terribly but the key things are the sorting of the data and the 'previous' function in a preceding load.

Hope that helps,

Matt - Visual Analytics Ltd

Problem counting the last modified data only

Wed, 16 Mar 2011 08:57:15 GMT

Thank you, that helped me half way.

How do I do it in the graph expression?

Problem counting the last modified data only

matt_crowther — Wed, 16 Mar 2011 09:50:26 GMT

Not fully understanding the data I'd be looking for something like this:

Count(Distinct If(Country='Sweden' AND OrderDate>=ReleaseDateStart AND OrderDate<=ReleaseDateEnd AND Last_Modified_Record_Ind=1, HouseNumber))