topic Re: Data Transformation using Inter record functions in QlikView

Data Transformation using Inter record functions

sudhakar_budde — Sat, 16 Nov 2024 02:39:18 GMT

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.

Re: Data Transformation using Inter record functions

MayilVahanan — Fri, 23 Aug 2019 11:16:24 GMT

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;

Re: Data Transformation using Inter record functions

sudhakar_budde — Fri, 23 Aug 2019 14:35:22 GMT

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

Re: Data Transformation using Inter record functions

Brett_Bleess — Tue, 03 Sep 2019 21:55:11 GMT

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/ScriptRegularStatements/Load.htm

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

Re: Data Transformation using Inter record functions

Saravanan_Desingh — Wed, 27 May 2020 22:54:50 GMT

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;

Re: Data Transformation using Inter record functions

Saravanan_Desingh — Wed, 27 May 2020 22:56:07 GMT