Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

john9inno
Contributor

Line adding ETL

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 IDLine No Movement DateDays of stored in previous locationOriginal LocationCurrent Location
A121/01/2010                                                                 
  -  
LOC 1 LOC 2
A223/01/2010 2 LOC 2 WH 1
A31/02/2010                                                                  
  9
WH 1 LOC2
B122/01/2010                                                                 
  -  
LOC 1 WH1
C122/01/2010                                                                 
  -  
LOC 1 LOC 3
C223/02/2010                                                                
  32
LOC 3 WH 3
D12/02/2010                                                                 
  -  
LOC 1 LOC 2
D210/02/2010                                                                  
  8
LOC 2 WH 3
D321/02/2010                                                                
  11
WH 3 LOC 4
Tags (3)
1 Solution

Accepted Solutions

Re: Line adding ETL

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

3 Replies

Re: Line adding ETL

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
Contributor

Re: Line adding ETL

thanks so much for your valuable input.

Highlighted
jafari_ervin
Contributor II

Re: Line adding ETL

That's Great.

Community Browser