Creating Two Dimensions From One

[gizmceri]

Hello,

I have two ID dimensions in hand but the combination of these two tells me if it represents a Main Category or a Category under a Main Category. Let me explain with a small example below.

ParentCategoryId	CategoryId	Type
0	1	Main Category 1
0	2	Main Category 2
1	3	Category 1 under Main Category 1
2	5	Category 2 under Main Category 2
1	4	Category 3 under Main Category 1
2	6	Category 4 under Main Category 2

If ParentCategoryId is "0", it means it is a Main Category with the Id of CategoryId. If ParentCategoryId is other than "0", then it means it is a Category with the Id of CategoryId  under the Main Category with the Id of ParentCategoryId. 

I am trying to create two dimensions which are Main Category and Category. Later I will need to display these two dimensions  in the same row in order to show the Main Category and Category of the same item.

Thanks in advance 🙂

[ArnadoSandoval]

Hi @gizmceri 

I am not sure if I understand your rules, I will extend your table with the dimensions you are trying to define.

ParentCategoryId	CategoryId	Type	Main Category	Category
0	1	Main Category 1	1
0	2	Main Category 2	2
1	3	Category 1 under Main Category 1	3
2	5	Category 2 under Main Category 2	5
1	4	Category 3 under Main Category 1	4
2	6	Category 4 under Main Category 2	6

I probably misunderstood your rules; Would you please clarify them?

Regards,

[gizmceri]

Hi @ArnadoSandoval ,

I can conclude the table as below.

ParentCategoryId	CategoryId	Type	Main Category Id	Category Id
0	1	Main Category A	1
0	2	Main Category B	2
1	3	Category a (under Main Category A)		3
2	5	Category b (under Main Category B)		5
1	4	Category c (under Main Category A)		4
2	6	Category d (under Main Category B)		6

What I am actually trying to achieve is extracting Type into two dimensions as Main Category and Category based on their Id combinations.  I need to display Category with the Main Category it belongs to in the same row as follows.

Main Category Id	Main Category	Category Id	Category
1	Main Category A	3	Category a
2	Main Category B	5	Category b
1	Main Category A	4	Category c
2	Main Category B	6	Category d

Kind Regards,

[ArnadoSandoval]

@gizmceri 

Let me rephrase your question: You want to derive two dimensions from the column Type, Is this what you want to do?;  wow the problem with the column Type is you posted  it twice with different data, let me show:

Type (first post)	Type (second post)
Main Category 1	Main Category A
Main Category 2	Main Category B
Category 1 under Main Category 1	Category a (under Main Category A)
Category 2 under Main Category 2	Category b (under Main Category B)
Category 3 under Main Category 1	Category c (under Main Category A)
Category 4 under Main Category 2	Category d (under Main Category B)

Now it is important to now the structure of the Type column, if my assumption is correct, the script will parse it, splitting it into two columns. Would you confirm how the column Type data actually looks like.

Regards,

[gizmceri]

Hi  @ArnadoSandoval ,

Sorry if I made you feel confused. I shared data with different names in order to make it more understandable. Let's continue with the latest version of data that you mentioned as "Type (second post)". 

You want to derive two dimensions from the column Type, Is this what you want to do? - Yes, this is exactly what I am trying to do. 

Thank you in advance!

[ArnadoSandoval · Accepted solution]

@gizmceri 

This is a parsing exercise with the Type column; the script is implementing the following Qlik's features/functions:

• Preceding LOAD 
• SubStringCount 
• Replace 
• You should already be familiar with Sign(); Upper()

The parsing got complicated because the text in the Type column shared the word 'Category' for the Main Category and Category dimensions; that is the reason seven levels of preceding loads where required; each level of preceding load perform a parsing action and I did not drop any calculation column for you to follow the logic; It could be possible these parsing loads could be simplified, but it will be up to you to take that task,

The script is parsing the text based on the known attributes, which are containing the text 'Main Category #' and 'Category #'; the process is not case sensitive either, below is the script:

NoConcatenate

Work_Types:
Load RowNo() As Row, Type Inline [
Type
Main Category A
Main Category B
Category a (under Main Category A)
Category b (under Main Category B)
Category c (under Main Category A)
Category d (under Main Category B)
];

NoConcatenate 
Types:
Load *,
     Replace(Main_Category_Work, ')', '') As Main_Category
     ;
Load *,
     if(Has_Main, 'Main Category ' & Trim(SubField(Work_Type_1, '|', 2)) , 'ERROR') As Main_Category_Work,
     If(Has_Cat = 1, 'Category ' & Trim(SubField(SubField(Work_Type_2, '|', 2), '(',1)), 'ERROR') As Category_Work;
     
Load *,
     if(Has_Cat = 1, Replace(Upper(Work_Type_1), 'CATEGORY', 'CK|'), Work_Type_1)   As Work_Type_2;
     
Load *, 
     Sign(SubStringCount(Upper(Work_Type_1), 'CATEGORY')) As Has_Cat;

Load *,
     if(Has_Main = 1, Replace(Upper(Type), 'MAIN CATEGORY', 'MK|'), '')   As Work_Type_1;
     
Load *,
     Sign(SubStringCount( Upper(Type), 'MAIN CATEGORY')) As Has_Main
     
Resident Work_Types;

Drop Table Work_Types;

The screenshot below are the results:

Please let me know if you have any question.

[gizmceri]

Hi @ArnadoSandoval ,

This is great, thank you very much for your help!

Best Regards,