How to build Product Hierarchy based on Product ID

[#1]

Hi Everyone,

I want to build product hierarchy structure based on Product ID. I have Product ID from table1. I want to produce table2.

Product ID
A.A.A.A.A
B.B.B.B.B
A.B.C
B.A
C.B.F.G
A.A.B.C

Product ID	Level1	Level2	Level3	Level4	Level5
A.A.A.A.A	A	A.A	A.A.A	A.A.A.A	A.A.A.A.A
B.B.B.B.B	B	B.B	B.B.B	B.B.B.B	B.B.B.B.B
A.B.C	A	A.B	A.B.C
B.A	B	B.A
C.B.F.G	C	C.B	C.B.F	C.B.F.G
A.A.B.C	A	A.A	A.A.B	A.A.B.C

Thank you very much for help!

[#2]

Clever,

When I test your script, the program cannot get level 5 value. Very interesting.

Best regards.

[Clever_Anjos]

I´m supposing that level 5 when product has 5 codes should be null,

Easy to fix, need it?

[#4]

Yes, please. I need max level value for each row.

[Clever_Anjos]

Kindly test this version and check if fits your needs:

t:
LOAD 
  [Product ID],
  SubStringCount([Product ID],'.')+1 as HowManyLevels
FROM
[https://community.qlik.com/thread/218664]
(html, codepage is 1252, embedded labels, table is @2);


let max=0;
for i = 0 to NoOfRows('t') -1
  let h = Peek('HowManyLevels',i,'t');
  let max = RangeMax(h,max);
  let p = Peek('Product ID',i,'t');
  temp:load null() as n AutoGenerate 0;
  for j = 1 to $(h) 
  Concatenate(temp)
  LOAD
  '$(p)' as [Product ID],
  'Level$(j)' as [Level],
  if(
  len(left('$(p)',Index('$(p)','.',$(j))-1)) >0,
  left('$(p)',Index('$(p)','.',$(j))-1),
  '$(p)'
  ) as Value
  AutoGenerate 1;
  next
next;
drop Field n;
drop Table t;


Final:
Generic LOAD
[Product ID],
Level,
Value
Resident temp;
drop Table temp;


LET vLoop = NoOfTables() - 1;
//Rename first table name
RENAME Table Final.Level1 to Final;

FOR i = 1 to vLoop
LET vCurrentTable = 'Final.Level'& ($(i) + 1);
Outer Join(Final)
LOAD  *
Resident $(vCurrentTable);
DROP Table $(vCurrentTable);  
NEXT

[#6]

Thank you very much. I like you use SubStringCount function to find the level information. Very very smart solution.

Appreciate your help.

Best regards.

[#7]

Hello Clever and settu_periasamy,

Your script is working for small data set. I have 50 million row need to load and it fail and lock down the whole system. Do you have any idea to make this script work for large data set?

Thank you very much!

[Clever_Anjos]

Process small chunks of data each time

[#9]

Thank you - Clever!

I run each level at a time and not use the for loop. The script looks working.

Thank you very much - Clever!