Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Re: Display records for latest date

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

12 Replies

Re: Display records for latest date

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?

Re: Display records for latest date

This what I got for your sample data:

Capture.PNG

Not applicable

Re: Display records for latest date

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

Re: Display records for latest date

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

Re: Display records for latest date

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

Re: Display records for latest date

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

Re: Display records for latest date

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

Not applicable

Re: Display records for latest date

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

Re: Display records for latest date

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

Community Browser