Line adding ETL

[john9inno]

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

[marcus_sommer · Accepted solution]

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

[john9inno]

thanks so much for your valuable input.

[jafari_ervin]

That's Great.