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

[#2]

Sure,

Data :

Product

From

To

  A            2014-01-01    9999-12-31

  A            2015-01-11    9999-12-31

  B            2014-05-05    9999-12-31

  B            2016-05-12    9999-12-31

  B            2015-05-21    9999-12-31

  C            2014-02-09    9999-12-31

  C            2015-02-19    9999-12-31

  C            2013-03-27    9999-12-31

  D            2016-04-16    9999-12-31

  D            2012-04-06    9999-12-31

The expected output is,

Product

From

To

A            2015-01-01    9999-12-31  

C            2015-02-19    9999-12-31

Products B and D should not be displayed as their Maximum of 'From' are not older than 180 days from today's date.

For B:

Num( 2016-06-03 - 2016-05-12) = 23

For 😧

Num(2016-06-03 - 2016-04-16) = 49

Hope you got it.

Thanks,

James

[sunny_talwar]

Try like this:

Table:

LOAD *

Where From < Today() - 180;

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

Group By Product;

LOAD * INLINE [

    Product,  From,          To

      A,      2014-01-01,    9999-12-31

      A,      2015-01-11,    9999-12-31

      B,      2014-05-05,    9999-12-31

      B,      2016-05-12,    9999-12-31

      B,      2015-05-21,    9999-12-31

      C,      2014-02-09,    9999-12-31

      C,      2015-02-19,    9999-12-31

      C,      2013-03-27,    9999-12-31

      D,      2016-04-16,    9999-12-31

      D,      2012-04-06,    9999-12-31

];

[#4]

Thanks a TON !

Perfect solution !!!

- James