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!

[Clever_Anjos]

Maybe

LOAD

[Product ID],

subfield([Product ID],'.',1) as Level1,

subfield([Product ID],'.',1) & '.' & subfield([Product ID],'.',2) as Level2,

subfield([Product ID],'.',1) & '.' & subfield([Product ID],'.',2) & '.' & subfield([Product ID],'.',3) as Level3,

and so on

[#3]

Thank you - Clever.

I think it got close. But A.B.C stoped at level 3. I don't want it show on level 4.

Please see attached qvw.

Appreciate!

[#4]

forgot attach my qvw. Here it is. Thanks.

[Clever_Anjos]

Please use an if to test, for example:

if(subfield([Product ID],'.',4) <> '',subfield([Product ID],'.',1) & '.' & subfield([Product ID],'.',2) & '.' & subfield([Product ID],'.',3)& '.' & subfield([Product ID],'.',4)) as Level4,

[#6]

Thanks - Clever!

The product level will increase in the future. Is there a way to automatically add level to this table?

[settu_periasamy · Accepted solution]

Hi,

May be try this in a dynamic way

T1:
LOAD *,Len([Product ID]) as Len;
LOAD * inline [
Product ID
A.A.A.A.A
B.B.B.B.B
A.B.C
B.A
C.B.F.G
A.A.F.G
] ;

  Temp:
LOAD Max(Len) as Len Resident T1;
Let vMaxLen=Peek('Len')+1;
DROP Table Temp;

Let v1='';
for i= 1 to vMaxLen step 2
j=Ceil(i/2);
New:
$(v1)
LOAD [Product ID],
if(Len([Product ID])>=$(i), Left([Product ID],$(i)),'') as Level$(j)
Resident T1;
Let v1='Left Join(New)';

NEXT i

DROP Table T1;

Edited: Red color highlighted

[Clever_Anjos]

A not optimized option, maybe someone can bring us a better idea:

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 1;
  for j = 1 to $(h)
  Concatenate(temp)
  LOAD
  '$(p)' as [Product ID],
  left('$(p)',Index('$(p)','.',$(j))-1)  as Level$(j)
  AutoGenerate 1;
  next
next;


Final:
LOAD
  [Product ID],
  MinString(Level1) as Level1
Resident temp
where len([Product ID])>0
group by [Product ID];
for i = 1 to max
  LEFT join(Final)
  LOAD
  [Product ID],
  MinString(Level$(i)) as Level$(i)
  Resident temp
  where len([Product ID])>0
  group by [Product ID];
next;
drop Table temp;

[Clever_Anjos]

Another solution using generic (and later rejoin)

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],
  left('$(p)',Index('$(p)','.',$(j))-1)  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

[#10]

Thank you very much - settu_periasamy and Clever.

Appreciate you provide such a very smart way to build my hierarchy structure.

Best regards.