Linking data in Qlikview from CSV where key is on a seperate row

[piri1069]

Hello experts,

I have a csv file where details of the key are on a different row.  I want to be able to link the preceeding key to the data rows after until the next key point.

For instance data:

2Key1

3Detail1aDetail1bDetail1c

3Detail2

3Detail3

2Key2

3Detail1

2Key3

3Detail1

3Detail2

Each detail is in a separate row after the tow with the key.  The data is pulled into a single column.  I am able to create the key table and a detail table in Qlikview, however, I struggle on linking the details to the corresponding key.  There is no unique number of details after the key.  I've looked at using the Peek() and the Previous() functions, but cant see how to bring it together.

Any and all assistance will be greatly appreciated.

Thanks,

Pedro.

[piri1069 · Accepted solution]

I figure it out.  This method seems a bit busy, but it achieved what I needed.  If anyone knows of a more efficient way to achieve the results, I would love if anyone can share.

// Because my prior life is in programming, I created variables.

Let iCount      =    1               ;
Let iMbrRow          =    1               ;
Let iDetailRow  =    1               ;
Let vRecType         =    'Default'       ;
Let vMaxRows         =    NoOfRows('Step1')    ;

NoConcatenate

// I put the data I need in a separate table

Step2:
LOAD
     RecType
     , RowNbr
     , MbrRow
Resident Step1
;

// I created a loop through the  rows of data...since it goes through each row, a large data set could take more time to //complete

For iCount = 0 to vMaxRows
    

//peek each row and place in variable
     Let vRecType = Peek('RecType',iCount,'Step2')  ;
    

// if member then capture row place in variable
     If vRecType = 'Member' Then
           Let iMbrRow = iCount +1;
     ELSEIF vRecType = 'Detail'
           Let iDetailRow = $(iMbrRow);
     ELSE
           Let iMbrRow = 0 ;
     End If;
    

// Goes by each row and rowlink to rownbr
     DetailLink:
     LOAD Distinct
           RowNbr
           , IF(Match(RecType,'Detail','Member'),$(iDetailRow))     as RowLink

     Resident Step2
     WHERE RowNbr = $(iCount)
     ;
    
Next iCount     ;  //cycle through all rows