peek and index columns

[SoillBIQlikSense]

Good day

I need to index and match columns

I have a table that looks like this

I would like to peek and index a new column with like this... to have new column ax XXXXX  in lineNo 0 to 3 and AAAAA in lineNo 4 to 6.I tried this but I get the wrong results

Test:
LOAD * inline [
Doc|ItemCode|LineNo|TreeType
1|XXXXX|0|S
1|BP1|1|I|
1|BP2|2|I|
1|BP3|3|I|
1|AAAAA|4|S
1|SP2|5|I|
1|SP3|6|I|
1|SP5|7|I|
1|SP6|8|I|
] (delimiter is '|');
Test2:
Load
Doc,ItemCode,LineNo,TreeType,
IF(peek(Doc,-1)=Doc and TreeType = 'I' ,peek(ItemCode),ItemCode)
as peeksresults1
Resident Test order by LineNo;

Thank you

[Kushal_Chawda]

@SoillBIQlikSense  do you always have two Tree Type?

[SoillBIQlikSense]

Yes ,S is the Father Tree type and the I are the children. The children must be linked to the right father, eg. XXXXX the children are 0,1,2,3 so the new column must be XXXXX for these lines and be AAAAA for line 5,6.7,8 Thank you

[SoillBIQlikSense]

www.soill.co.za
www.bwellfoods.co.za

[Kushal_Chawda · Accepted solution]

@SoillBIQlikSense  try below

Load
Doc,ItemCode,LineNo,TreeType,
IF(TreeType = 'S' ,ItemCode,peek(ItemCodeNew)) as ItemCodeNew
as peeksresults1
Resident Test order by LineNo;

[SoillBIQlikSense]

Thank you , a big thank you.This works 100%.