Split Column into multiple Columns Dynamically Based on Condition

[KKumar92]

Hi All,

I have a BOM File as follow,

Semi-Finished|Finished_Good_No	Semi-Finished|Finished_Good_Type	Raw-Material_No	Raw-Material_Type	Cumulative_Qty	unit_of_measure
FG001200100	Finished Good	RM001500100	Raw Material	0.001	kg
FG001200100	Finished Good	RM001500101	Raw Material	0.45	kg
FG001200100	Finished Good	RM001500102	Raw Material	0.05	kg
FG001200100	Finished Good	RM001500103	Raw Material	0.65	kg
FG001200100	Finished Good	RM001500104	Raw Material	1	kg
FG001200100	Finished Good	SF008600100	Semi-Finished Good	1	unit
FG001200100	Finished Good	SF008600101	Semi-Finished Good	1	unit
SF008600100	Semi-Finished Good	RM001500100	Raw Material	0.7	kg
SF008600100	Semi-Finished Good	RM001500101	Raw Material	0.65	kg
SF008600100	Semi-Finished Good	RM001500109	Raw Material	0.4	kg
SF008600100	Semi-Finished Good	RM001500110	Raw Material	1.25	kg
SF008600100	Semi-Finished Good	SF008600121	Semi-Finished Good	1	unit
SF008600101	Semi-Finished Good	RM001500100	Raw Material	0.25	kg
SF008600101	Semi-Finished Good	RM001500101	Raw Material	0.003	kg
SF008600101	Semi-Finished Good	RM001500102	Raw Material	0.55	kg
SF008600101	Semi-Finished Good	RM001500107	Raw Material	0.8	kg
SF008600121	Semi-Finished Good	RM001500106	Raw Material	0.001	kg
SF008600121	Semi-Finished Good	RM001500117	Raw Material	0.076	kg
SF008600121	Semi-Finished Good	RM001500118	Raw Material	0.009	kg
SF008600121	Semi-Finished Good	RM001500119	Raw Material	0.089	kg

In the above table it specifies the Raw Material and Semi-Finished Goods Required to manufacture either Finished Goods or Semi-Finished Goods.

My requirement is to breakdown the above table to multiple level according the production, and drill down till the last level as follow;

Finished_Good	Level1_RM_Required	Cumulative_Qty	unit_of_measure	Level2_RM-Required	Cumulative_Qty	unit_of_measure	Level3_RM-Required	Cumulative_Qty	unit_of_measure
FG001200100	RM001500100	0.001	kg	N/R			N/R
	RM001500101	0.45	kg	N/R			N/R
	RM001500102	0.05	kg	N/R			N/R
	RM001500103	0.65	kg	N/R			N/R
	RM001500104	1	kg	N/R			N/R
	SF008600100	1	unit	RM001500100	0.7	kg	N/R
				RM001500101	0.65	kg	N/R
				RM001500109	0.4	kg	N/R
				RM001500110	1.25	kg	N/R
				SF008600121	1	unit	RM001500106	0.001	kg
							RM001500117	0.076	kg
							RM001500118	0.009	kg
							RM001500119	0.089	kg
	SF008600101	1	unit	RM001500100	0.25	kg	N/R
				RM001500101	0.003	kg	N/R
				RM001500102	0.55	kg	N/R
				RM001500107	0.8	kg	N/R

In the above table, I have drill down till only 3 levels, however this could be changed according to the product.

Please advise how this could be achieved on qlik sense.  Please find the attached image before for a better understanding.

[morgankejerhag · Accepted solution]

This is potentially a quite advanced problem. Would it work for you to join and rename the fields a number of times? Like

Load
  [Semi-Finished|Finished_Good_No] as Finished_Good,
  Raw-Material_No as MaterialNo1,
  Cumulative_Qty as Cumulative_Qty1
from BOM.qvd (qvd) where [Semi-Finished|Finished_Good_Type]='Finished Good';

left join
Load
  [Semi-Finished|Finished_Good_No] as MaterialNo1,
  Raw-Material_No as MaterialNo2,
  Cumulative_Qty as Cumulative_Qty2
from BOM.qvd (qvd);

left join
Load
  [Semi-Finished|Finished_Good_No] as MaterialNo2
  Raw-Material_No as MaterialNo3,
  Cumulative_Qty as Cumulative_Qty3
from BOM.qvd (qvd);

And so on...

Morgan Kejerhag
Senior Business Intelligence Consultant
Drake Analytics
http://www.drakeanalytics.se

[Saravanan_Desingh · Accepted solution]

Try like this,

tab1:
Hierarchy([Raw-Material_No],[Semi-Finished|Finished_Good_No],Name,,[Semi-Finished|Finished_Good_No],'Path','/',Depth)
LOAD *, [Raw-Material_No] As Name;
LOAD * INLINE [
    Semi-Finished|Finished_Good_No, Semi-Finished|Finished_Good_Type, Raw-Material_No, Raw-Material_Type, Cumulative_Qty, unit_of_measure
    FG001200100, Finished Good, RM001500100, Raw Material, 0.001, kg
    FG001200100, Finished Good, RM001500101, Raw Material, 0.45, kg
    FG001200100, Finished Good, RM001500102, Raw Material, 0.05, kg
    FG001200100, Finished Good, RM001500103, Raw Material, 0.65, kg
    FG001200100, Finished Good, RM001500104, Raw Material, 1, kg
    FG001200100, Finished Good, SF008600100, Semi-Finished Good, 1, unit
    FG001200100, Finished Good, SF008600101, Semi-Finished Good, 1, unit
    SF008600100, Semi-Finished Good, RM001500100, Raw Material, 0.7, kg
    SF008600100, Semi-Finished Good, RM001500101, Raw Material, 0.65, kg
    SF008600100, Semi-Finished Good, RM001500109, Raw Material, 0.4, kg
    SF008600100, Semi-Finished Good, RM001500110, Raw Material, 1.25, kg
    SF008600100, Semi-Finished Good, SF008600121, Semi-Finished Good, 1, unit
    SF008600101, Semi-Finished Good, RM001500100, Raw Material, 0.25, kg
    SF008600101, Semi-Finished Good, RM001500101, Raw Material, 0.003, kg
    SF008600101, Semi-Finished Good, RM001500102, Raw Material, 0.55, kg
    SF008600101, Semi-Finished Good, RM001500107, Raw Material, 0.8, kg
    SF008600121, Semi-Finished Good, RM001500106, Raw Material, 0.001, kg
    SF008600121, Semi-Finished Good, RM001500117, Raw Material, 0.076, kg
    SF008600121, Semi-Finished Good, RM001500118, Raw Material, 0.009, kg
    SF008600121, Semi-Finished Good, RM001500119, Raw Material, 0.089, kg
];

[Saravanan_Desingh]

Output:

[KKumar92]

Hi Morgan,

Thanks for the solution provided, and it works for the requirement I have given above. As you have mentioned, the number of levels could vary according to the product for example;

Product A can have 3-level breakdown, while the Product B has 5-level breakdown. Is there any possibilities to make this script dynamic than making it static?

I am working on it, and appreciate you inputs as well.

Thanks,

Kishore

[KKumar92]

Hi Saran,

Thanks for the solution given above, this works fine perfectly for both static and dynamic datasets.

Thanks,

Kishore