Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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