Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Display records for latest date

I have the following data in my QVD

ProductFromTo
DEE00105952015-02-032015-03-03
DEE00105952015-03-042015-12-31
DEE00105952015-04-179999-12-31
DEE00105952016-01-019999-12-31
DEE00124742015-03-042015-12-31
DEE00124742016-01-019999-12-31
DEE00128812015-03-032015-03-03
DEE00128812015-03-042015-12-31
DEE00128812016-01-019999-12-31
DEE00131962015-12-302015-12-31
DEE00131962016-01-019999-12-31
DEE00131962016-01-019999-12-31
DEE00138042015-03-042015-12-31
DEE00138042016-01-019999-12-31
DEE00313752015-12-302015-12-31
DEE00313752016-01-019999-12-31
DEE00313752016-01-019999-12-31
DEE00387292015-03-042015-12-31
DEE00387292016-01-019999-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

DEE00105952016-01-019999-12-31
DEE00124742016-01-019999-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

1 Solution

Accepted Solutions
sunny_talwar

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;


Capture.PNG

View solution in original post

12 Replies
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:

Capture.PNG

Not applicable
Author

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

DEE00105952016-01-019999-12-31
DEE00124742016-01-019999-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

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;


Capture.PNG

Not applicable
Author

distinct error.PNG

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

Not applicable
Author

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

Not applicable
Author

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?