Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Can any one help me with my following requirement please?
I have the below data
SnapshotDate | Product | StartedDate | EndDate |
07/01/2018 | A | 01/01/2018 | - |
25/02/2018 | A | - | 03/03/2018 |
11/03/2018 | A | 05/03/2018 | - |
18/03/2018 | A | - | 24/03/2018 |
01/04/2018 | A | 26/03/2018 | 07/04/2018 |
15/04/2018 | A | 09/04/2018 | - |
11/08/2019 | A | - | 30/12/2099 |
I want like this
Product | StartedDate | EndDate |
A | 01/01/2018 | 03/03/2018 |
A | 05/03/2018 | 24/03/2018 |
A | 26/03/2018 | 07/04/2018 |
A | 09/04/2018 | 30/12/2099 |
Thanks in advance.
SB
Hi Mayilvahan,
Thanks for your response!
The code is working only for the data set that I provided here. But this is a subset of data in a bigger table where there many other Products. So, somehow it's not working on the whole data set!
I tried this but it's not working:
Load
Product,
StartedDate
,if(Product=Peek(Product) and Len(Trim(Previous(EnddDate)))>0, Previous(EnddDate), EnddDate) as EndDate
Resident Temp
where Len(Trim(StartedDate)) > 0 order by SnapshotDate desc;
Thanks
SB
Sudhakar, try the following Help link, you should find what you need there I believe, not sure the Resident is what you need here, suspect you need an actual data source like an Excel file etc...
There are a bunch of examples at the bottom of the link, just expand those to find the ones that may help you here.
Regards,
Brett
One solution is..
SET DateFormat='DD/MM/YYYY';
tab1:
LOAD RowNo() As RowID, * INLINE [
SnapshotDate, Product, StartedDate, EndDate
07/01/2018, A, 01/01/2018,
25/02/2018, A, , 03/03/2018
11/03/2018, A, 05/03/2018,
18/03/2018, A, , 24/03/2018
01/04/2018, A, 26/03/2018, 07/04/2018
15/04/2018, A, 09/04/2018,
11/08/2019, A, , 30/12/2099
];
tab2:
LOAD *, RowNo() As K1
Resident tab1
Where StartedDate<>''
;
tab3:
LOAD *, RowNo() As K2
Resident tab1
Where EndDate<>''
;
tab4:
NoConcatenate
LOAD Product, StartedDate, K1
Resident tab2;
Left Join(tab4)
LOAD Product, EndDate, K2 As K1
Resident tab3;
Drop Table tab1, tab2, tab3;
Drop Field K1;