creating a drill down data using in Qliksense data load editor

[Qlikuser09]

I had a pivot table kind of data. i did a cross table and now I have a table with three rows like this.

Sample data

Company code	Attributed fields	Count
AA	Manager	2
AA	CIO	1
AA	IT Staff	1
AA	Dev Staff	1
AA	Management Total	3
AA	Staff Total	2
AB	Manager	5
AB	CIO	2
AB	IT Staff	3
AB	Dev Staff	2
AB	Management Total	7
AB	Staff Total	5

Interpretation of the data

Management total - (Manager + CIO)

Staff Total(IT Staff+Dev Staff).

I have to make the tables function well with the front end, such that, if I select the management total, the other chart should display the details with the Manager and CIO. 

Any ideas/suggestions on how to separate the data fields .

[abhijitnalekar]

Hi @Qlikuser09 ,

This can be done in multiple ways but let me know which approach you would like to use from below

Dou likes to make a static chart or you want to select Management or staff in any filter.

[Qlikuser09]

i want to rearrange my table, to work well with Qliksense, for now, I'm able to create only static charts. I want dynamic ones

[abhijitnalekar]

Hi @Qlikuser09 ,

Please follow the below steps

1. create a new dimension at the script level.  

if(match("Attributed fields",'Management','Manager','CIO')>1,'Management','Staff') as newDim,

2. GO to Master Items. Create New dimension as below

3. Add Hierarchy in the table along with the measure.

Let me know if this solves your purpose.

[Qlikuser09]

if(match("Attributed fields",'Management','Manager','CIO')>1,'Management','Staff') as newDim, this should be added as a varialble or should be added in the data load editor

[abhijitnalekar]

Add in the Data load Editor.

[Qlikuser09]

if(match("Attributed fields",'Management','Manager','CIO')>1,'Management','Staff') as newDim, i tried adding this.  however I am  getting error after "if(match("Attributed fields",'Management','Manager','CIO')>1," 

[abhijitnalekar]

"Attributed fields" Is this your filed name in the real dataset?

[Qlikuser09]

yes , that's the field name, (management total and staff total) are the hierarchy   level 1 and (manager, CIO, It staff, Dev staff are the level 2 hierarchy)

[abhijitnalekar]

Can you share the exact error which you are getting after loading the script