Display records for latest date

[#1]

I have the following data in my QVD

Product	From	To
DEE0010595	2015-02-03	2015-03-03
DEE0010595	2015-03-04	2015-12-31
DEE0010595	2015-04-17	9999-12-31
DEE0010595	2016-01-01	9999-12-31
DEE0012474	2015-03-04	2015-12-31
DEE0012474	2016-01-01	9999-12-31
DEE0012881	2015-03-03	2015-03-03
DEE0012881	2015-03-04	2015-12-31
DEE0012881	2016-01-01	9999-12-31
DEE0013196	2015-12-30	2015-12-31
DEE0013196	2016-01-01	9999-12-31
DEE0013196	2016-01-01	9999-12-31
DEE0013804	2015-03-04	2015-12-31
DEE0013804	2016-01-01	9999-12-31
DEE0031375	2015-12-30	2015-12-31
DEE0031375	2016-01-01	9999-12-31
DEE0031375	2016-01-01	9999-12-31
DEE0038729	2015-03-04	2015-12-31
DEE0038729	2016-01-01	9999-12-31

I have loaded all three columns in my report and displaying them as dimension. My requirement is to display the products (without duplicates) with latest 'from' date.

For Example:

P                    From          To

DEE0010595	2016-01-01	9999-12-31
DEE0012474	2016-01-01	9999-12-31

and so on....

Can this be done without changing the following load script ?

TABLE:

LOAD Product AS P,

     date(max(From),'YYYY-MM-DD') AS From,

     To AS To

FROM [table.qvd](qvd)

WHERE Interval(date(Today(),'MM/DD/YYYY') - (date(AddMonths(Date(Today()), -6),'MM/DD/YYYY')),'d') >= 180

GROUP BY Product, To;

TIA,

James

[sunny_talwar]

May be like this:

TABLE:

LOAD Product AS P,

     Date(Max(Date#(From, 'YYYY-MM-DD')), 'YYYY-MM-DD') as From,

     FirstSortedValue(Date#(To, 'YYYY-MM-DD'), Date#(From, 'YYYY-MM-DD')) as To

FROM [table.qvd](qvd)

Group By Product;

Not sure what the intent of the where statement is?

[sunny_talwar]

This what I got for your sample data:

[#4]

Thanks for your support Sunny , but the 'To' column is sorted separately, which changed the original 'To'values (which is wrong). As i said before for every product latest 'From' date shoud be identified and the entire row should be displayed. (See samples below)

Product                 From          To

DEE0010595	2016-01-01	9999-12-31
DEE0012474	2016-01-01	9999-12-31

For the product DEE0010505 latest 'From' date out of 3 entries is '2016-01-01' and the corresponding 'To' date is '9999-12-31' . But in your result it was changed as '2015-03-03' which is not correct. Can you please look into it ?

TIA,

James

[sunny_talwar · Accepted solution]

My bad, here is the new code:

Table:

LOAD Product as P,

    Date(Max(Date#(From, 'YYYY-MM-DD')), 'YYYY-MM-DD') as From,

    FirstSortedValue(DISTINCT Date#(To, 'YYYY-MM-DD'), -Date#(From, 'YYYY-MM-DD')) as To

FROM

[https://community.qlik.com/thread/218554]

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

Group By Product;

[#6]

My QlikView is not detecting 'DISTINCT ' keyword in blue color inside 'FirstSortedValue' context . Please help.

FYI: I even copied your new code and tried. But no luck

Thanks,

James

[sunny_talwar]

Even though it doesn't detect the DISTINCT keyword, have you tried reloading the application? There have been times when QlikView expression editor won't recognize something as correct syntax, but it would run through (and distinct with FirstSortedValue did use to have that problem). Give it a shot by running it and see what you get

[#8]

Yaay ! It works !! Thanks a lot ! You are a GEM !!!  

[#9]

Hello stalwar1,

There is a small change to be done as follows.

     - Latest 'From' date per product should be displayed which should be 180 days older from today.

     - I think the line 'WHERE Interval(date(Today(),'MM/DD/YYYY') - (date(AddMonths(Date(Today()), -6),'MM/DD/YYYY')),'d') >= 180' which i added in the original post should work. Isn't it ?

TIA,

James

[sunny_talwar]

Would you be able to elaborate what the expected output will look like in the sample above?