Qlik Requirement

[jyothish8807]

Hi All,

I have two tables associated to each other based on "Name" field and as the per data one user can have more that two profiles.

What i want is to create a staked bar chart, with Month as 1st dimension, Profile as 2nd dimension and sum of Sale as measure.

The catch is if a user (eg A) have two profiles, then when creating the Bar chart, the user A should be counted under "Profile 1" only. The same goes for User B but User C should be considered under Profile 2 as he have only 1 Profile.

I would like to achieve this in front end. Thank you in advance !

		Table 1
Month	Name	Sal
Jan	A	10
Jan	B	12
Feb	A	20
Feb	B	20
Feb	C	10

	Table 2
Name	Profile
A	Profile 1
A	Profile 2
B	Profile 1
B	Profile 2
C	Profile 2

Expected O/P

Month	Name	Profile	Sal
Jan	A	Profile 1	10
Jan	B	Profile 1	12
Feb	A	Profile 1	20
Feb	B	Profile 1	20
Feb	C	Profile 2	10

[Gysbert_Wassenaar]

Something like this perhaps:

Load Month,Name,Sal From Table1;

LEFT JOIN (Table1)

Load Name, MinString(Profile) as Profile From Table2 Group By Name;

[jyothish8807]

Hi @Gysbert_Wassenaar ,

Thank you for your suggestion, but i cannot do a join in the script because of my current data model.Also in my other reports i will have to show that User A have two profiles.

How can we achieve this in front end.

[tresB]

Like this?

[jyothish8807]

Hi @tresB ,

Yes, something similar but i cannot use min or max string as this ia an example and my actual Profile names may vary. Yes, but we can hard code "Profile 2" to be excluded if the user have any other Profile along with Profile 2.

[CELAMBARASAN]

Will this work for you?

Coalesce(MinString({<Profile={'Profile 1'}>}Profile),MinString(Profile))

[tresB]

Hi Jyothish,

So the logic would be like - exclude only 'Profile 2' if there are multiple profiles per month, per name. That means - if there are four profiles for one month and one name - only one (profile 2) should be removed and rest all remain ?

[jyothish8807]

No, this doesn't help. Thank you for your input  🙂