Line adding ETL

john9inno — Mon, 27 Jul 2015 06:11:31 GMT

Hi all,

i just have a quetion which is very tricky.

i am getting data in CSV that has header and line items are combined into one single record.

but i need to seperate header information from line items and store that into QVD. also, need to add some additional data into it.

additional data field. Line item number, Date of each movement, Days of store in previous location (if the record is initial record then zero)

thanks.

John

for example.

Original DATA

ID, Description

A, Date 2010/01/21 - move from LOC 1 to LOC 2, Date 2010/01/23 - move from LOC 2 to WH 1, Date 2010/02/01 - move from WH 1 to LOC2

B, Date 2010/01/22 - move from LOC 1 to WH1

C, Date 2010/01/22 - move from LOC 1 to LOC 3, Date 2010/02/23 - move from LOC 3 to WH 3

D, Date 2010/02/02 - move from LOC 1 to LOC 2, Date 2010/02/10 - move from LOC 2 to WH 3, Date 2010/02/21 - move from WH 3 to LOC 4

target structure

Header ID	Line No	Movement Date	Days of stored in previous location	Original Location	Current Location
A	1	21/01/2010	-	LOC 1	LOC 2
A	2	23/01/2010	2	LOC 2	WH 1
A	3	1/02/2010	9	WH 1	LOC2
B	1	22/01/2010	-	LOC 1	WH1
C	1	22/01/2010	-	LOC 1	LOC 3
C	2	23/02/2010	32	LOC 3	WH 3
D	1	2/02/2010	-	LOC 1	LOC 2
D	2	10/02/2010	8	LOC 2	WH 3
D	3	21/02/2010	11	WH 3	LOC 4

Re: Line adding ETL

marcus_sommer — Mon, 27 Jul 2015 11:19:25 GMT

With a record-counter which parted the records in a while-loop and a few string-functions like subfield(), mid(), textbetween() to separate the different informations and peek() to compare the records against each other isn't it very difficult - see the attachment.

- Marcus

Re: Line adding ETL

john9inno — Tue, 28 Jul 2015 06:12:59 GMT

thanks so much for your valuable input.

Re: Line adding ETL

jafari_ervin — Tue, 28 Jul 2015 06:29:16 GMT

That's Great.

topic Line adding ETL in QlikView

Line adding ETL

Re: Line adding ETL

Re: Line adding ETL

Re: Line adding ETL