Qlik sense Scripting

[Mouni09]

Hi all, I have a 2 dataset 

Dataset1:

Employee, ID, Blood group, Age           

John,123,A,2

Jack,456,B,24

Jill,789,A,25

Jym,123,O,26

Zen,456,B,27

Dataset2:

Id,City

123,New york

456,Dallas

234,Michigan

789,Ohio

I want to display below requirements in KPI's'

Count of emploees from each city

What is the common blood group  ?

Who are from Same cities

Who is having unique blood group ?

please help

Thanks

[N30fyte]

Hi,

KPIs display individual counts - they answer the question 'How Many?'

Most of your questions ask  'Who' or 'What?'. Even in the case of 'Count of employees from each city?', there are several counts, not a an individual count, so the question isn't amenable to answering with a KPI. Even using Multi KPIs, if a new employee starts a new career in a new town, you would need to reconfigure your sheet.

Given the data you have posted, none of the questions can be answered, as the IDs aren't unique. I appreciate that you've just typed it in quickly, but if you want to work with it and get help with it then it needs to be valid data.

So give each employee a unique ID, and make sure you have the same number of rows in each table, and that the IDs are consistent across both tables.

Now ask yourself: which is the most common blood group if (as in your data) two employees are in group A and two employees are in group B? The answer is that there is no most common blood group - A and B are equally common.

With a much larger sample you could in theory use =mode([Blood group]) but the risk remains that there will be no absolute answer.

Once you've got your data sorted out, 'Count of employees from each city?' can be displayed in a straight table: Dimension: City. Measure: =count(ID)

You could use pivot tables to display the answers to the other questions. For example 'Who are from the same city?':

With Employee and City as the dimensions and =count(ID) as the measure. A similar approach would work with the most and least common blood groups. No doubt there are other ways of doing it. 😀

Hope some of this helps.