Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
];
Thanks a TON !
Perfect solution !!!
- James