Data Transformation using Inter record functions

[sudhakar_budde]

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

[MayilVahanan]

HI
Try like this


Temp:
LOAD * 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
];

Load Product, StartedDate, if(Len(Trim(Previous(EndDate)))>0, Previous(EndDate), EndDate) as EndDate Resident Temp
where Len(Trim(StartedDate)) > 0order by SnapshotDate desc;

DROP Table Temp;

[sudhakar_budde]

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

[Brett_Bleess]

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...

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...

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

[Saravanan_Desingh]

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;

[Saravanan_Desingh]