Doing a count on the latest available information on a table

[#1]

ID	Date	Yes or No
1	15-Mar-2012	Y
2	15-Mar-2012	Y
3	15-Mar-2012	Y
4	15-Mar-2012	Y
1	25-Mar-2012	N

Hello everyone,

I'm looking for a technique to pick out only the latest value in a table.

In the table above, ID number 1 has first a Y then a N attached to him/her.

If I do a count({$<YorN={'Y'}>} DISTINCT CIFNR) the result is 4 (four).

If I do a count({$<YorN={'N'}>} DISTINCT CIFNR) the result is 1 (one).

The sum of the above 2 (two) is 5 (five). The numbers I need to end up with is 3 for Y and 1 for N (a total of four). The first entry for ID=1 & Yes or No = Y, should be disregarded, as there is a new entry of N for ID=1 (Date=25-Mar-2012).

Since ID=1 has 2 entries, I only want to reference and include the latest value entered. What do I need to do in the script to be able to identify the latest entry, whilst not losing info on previous ones.

Many thanks for any help/advice.

Kr

Cheenu

[swuehl · Accepted solution]

If your Date is a recognized QV Date type with numerical representation, e.g. by loading your above table like

LOAD ID,

     date#(Date,'DD-MMM-YYYY') as Date,

     [Yes or No]

FROM

[http://community.qlik.com/thread/50410?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

You can calculate your numbers like

=sum(aggr(if(FirstSortedValue([Yes or No],-Date)='Y',1,0),ID))

resp.

=sum(aggr(if(FirstSortedValue([Yes or No],-Date)='N',1,0),ID))

e.g. in a text box.

Hope this helps,

Stefan

[jagan]

Hi,

Try this in script

Temp:

LOAD

    ID,

    Date(Date#(Date, 'DD-MMM-YYYY'), 'DD-MMM-YYYY') AS Date,

    YN;

LOAD * INLINE [

ID, Date, YN   

1, 15-Mar-2012, Y

2,    15-Mar-2012,    Y

3,    15-Mar-2012,    Y

4,    15-Mar-2012,    Y

1,    25-Mar-2012,    N];

Data:

LOAD

    ID,

    Date,

    YN AS [Yes/No]   

WHERE IsDup = 0;

LOAD

    ID,

    Date,

    YN,

    IF(Previous(ID) = ID, 1, 0) AS IsDup

RESIDENT Temp

ORDER BY ID, Date DESC;

DROP Table Temp;

Regards,

Jagan.

[#4]

Hi Stefan & Jagan,

Thank you for reminding me about FirstSortedValue function and the sort in script technique. However, neither works. I am trying to simplify the problem, but I have a big data set and I need QlikView to take the latest flagged value and then do a count based on what it the last posted flag value.

In my case example, ID=2, 3 and 4 should be counted the expression referring to flag Y (ID=1 must be excluded, as the latest flag is N) and ID=1 should be counted in an expression counting the N flag, as this is the latest (most recent) value entered.

If what I say makes sense, thanks for any further help/advice. Otherwise, thanks for taking the time to post your suggestions.

Cheers,

Cheenu

[swuehl]

Hi Cheenu,

I believe my suggested code is working with your posted sample data (and probably Jagan's code is working too, haven't tried though), see attached.

So I think there must something in your real data that is breaking our solutions and we need to detect this difference.

FirstSortedValue might have problems if you have several answers per date, but then you won't know the correct 'most recent' answer anyway.

Maybe you need to add some more dimensions to the aggr() function to calculate your aggregation on the granularity you need, maybe just the ID is not enough?

Regards,

Stefan

[#6]

Hi Stefan,

Thank you for the post and sample QVW. I had problems with the date/time stamp format, but now it works A-OK. Thanks for all the help.

Jagan: Thanks for the previous() technique. I need the duplicates, but the technique makes sense and I will try it together with Stefan's to make a more sustainable datamodel and flags.

Cheers to both of you.

Kr

C  ;o)