Count based on another Field

[mskusace]

I would like to count a column called "ID" based on the criteria of another column "Color". The problem is, I have another table that is associated and has the same "ID", but multiple instances because it has another field called "ROLE".

I would like to count the UNIQUE "ID"s based for "Color" =  "RED". It is actually the word "RED", so text.

The table below would yield 3 and 50%. How can I do this? I am trying to use the KPI chart.

ID	Color
WA13	GREEN
WB14	RED
WC15	ORANGE
ZS15	GREEN
DB09	RED

The other associated table

ID	ROLE	USER
WA13	MANAGER	USER1
WA13	SUPERVISOR	USER2
WA13	ANALYST	USER3
WB14	ANALYST	USER3
WB14	MANAGER	USER4
WC15	SUPERVISOR	USER5
WC15	MANAGER	USER6
WC15	SECRETARY	USER7
ZS15	MANAGER	USER4
ZS15	ANALYST	USER8
ZS15	SUPERVISOR	USER2
ZS15	SECRETARY	USER9
DB09	SECRETARY	USER10
DB09	SECRETARY	USER11
DB09	ANALYST	USER12

[abhi1693r]

Also, just to clarify one PART can have multiple CUBES. Just for simplicity I have taken one for each here.

[mskusace]

If you look at the example I posted, it has 1 part with 2 cubes associated with it. If you just do a count of "PARTS" based on that relationship and table structure, you will get a count of 4. Unless your logic has some other parameters. This is the way I am visualizing your data, which could be incorrect or maybe not the best way.

PART_ID	PART
1	SW1001
2	DC1003
3	BW1002

CUBES_ID	CUBES
1	1415TV
2	1714DC
3	1516TD

COMBO_ID	PART_ID	CUBE_ID
1	1	1
2	1	2
3	2	3
4	3

If you do a count of "COMBO_ID", then you will get 4. A new "COMBO_ID" will be created for each "PART" and "CUBE" combination.

[abhi1693r]

Well your solution seems like it will work, but I am really new to Qlikview.

Would you be able to guide me through the implementation?

Currently, I  have actually LEFT joined 2 tables to get my data. One of the tables has PART and the other table has PART and CUBES. 

[mskusace]

If your data is lining up properly, then the implementation should be fine. Create a table visualization in Qlik and put in 2 columns, "PART" and "CUBES". If everything is as you expect, then you can just do a count of  "PART".

[abhi1693r]

The Solution is working for some cases but for some its acting oddly.

PART	CUBES
0MATER
CNMM	CNMC01
CNMM	CNMC02
CNMM	CNMC03
CNMM	CNMC05
CNMM	CNMC06
CNMM	CNMC07
CNMM	CNMC08

I did a count(PART) and I am getting 10. Is this due to the LEFT JOIN I setup. As the table on the LEFT had more than one entry for PART=OMATER.

Thanks 

Abhishek

[mskusace]

Should there be more than one "0MATER" on the LEFT table? If so, are there any "CUBES" associated with any of the entries? Yes, in this case "0MATER" would all get collapsed into 1 entry for the table assuming the "CUBES" were the same for each of the "0MATER" which look blank in this case. If that is normal in your data, then we will have to do something else. Otherwise, you could just fix the data if it's an error.

[abhi1693r]

You are correct I have 2 'OMATER' values in the left table and hence the solution is not working comprehensively.

I do have a lot of values like 'OMATER' with blank CUBES, so should I do ?

[mskusace]

There very well may be a better way to do this as I am no expert nor have I had the opportunity to really learn the data load script editor. I would clean the data before loading it into Qlik. I would normalize the data. You pretty much want to count the unique combination of "PART+CUBES", which becomes "COMBO_ID" essentially in my example. You could create a table with a combination of "PART+CUBES" and THEN do a distinct count on that. 

[abhi1693r]

Is there a dummy script you might have that I could refer for this implementation? or Can this be done through the UI options?

[mskusace]

I would create a new Master Dimension and I would concatenate "PART" and "CUBES", so "PART&CUBES" which could be named "COMBO". Then I would keep your original table the same "PART" and "CUBES". I would create a KPI visualization that does a "COUNT(DISTINCT COMBO)".