topic Re: Show Multiple Fields Content based on a condition in QlikView

Show Multiple Fields Content based on a condition

s10157754 — Wed, 25 Nov 2020 16:16:04 GMT

Dear Qlivkiew Experts,

I have this set of database:

I would like to create a straight table to show rank1 & rank2 's value based on a condition.

My condition is: if Type is = "O" and Rank1/Rank2 is not equal to blank or "17". Then show the value from Rank1/Rank2 with the frequency of that value as well.

My current expression for the rank is: =Aggr(Only({<Type={"O"},Rank1-={"" ,"17" }>} Rank1), Rank1)

and the expression for frequency is: count({<[Type] = {"O"},Rank1-={"","17"}>} [Rank1])

But this can only return me with the value shown in Rank1 column, and I would like to have a combination of 2 fields.

My desired outcome would be sth look like below:

May I know what is the correct syntax to achieve my desired outcome?

Attached Qlikview excel file for your reference.

Best Regards

QianNing

Re: Show Multiple Fields Content based on a condition

MindaugasBacius — Thu, 24 Jan 2019 09:57:05 GMT

If your row data is like you mentioned:

Then the back end solutions works great:

Directory;
LOAD Type,
Rank1,
Rank2,
Alt(Rank1, Rank2) as newRank
FROM
Qv.xlsx
(ooxml, embedded labels, table is Sheet1);

Re: Show Multiple Fields Content based on a condition

s10157754 — Thu, 24 Jan 2019 10:39:21 GMT

Hi MindaugasBacius,
Thank you for your prompt reply.
As i only showing partial of the data set that I have, under Rank1 and Rank2, there are also possibilities that both column have different type of numbers or having the same number.
E.g Rank 1 Rank 2
9 8
12 47
12 9

In this case your solution might not working to achieve my desired outcome.
Best Regards
QianNing

Re: Show Multiple Fields Content based on a condition

MindaugasBacius — Thu, 24 Jan 2019 11:15:40 GMT

Could you then provide raw data sample?

Re: Show Multiple Fields Content based on a condition

sunny_talwar — Thu, 24 Jan 2019 12:39:33 GMT

So, if both have values which value would you want to show? Sum them? Find Max? Find Min? In that case you can try this

Sum

RangeSum(Rank1, Rank2) as newRank

Max

RangeMax(Rank1, Rank2) as newRank

Min

RangeMin(Rank1, Rank2) as newRank

Avg

RangeAvg(Rank1, Rank2) as newRank

Re: Show Multiple Fields Content based on a condition

s10157754 — Thu, 24 Jan 2019 12:57:14 GMT

Hi Mindaugasbacius,

Sorry for not explaining the question clearly.
I just want to display the value from Rank 1 & Rank 2 columns with count of the value as well.
For example:

So my chart would show like this:

Do not display the value if:

1)"Type" is equal to "O" and "Rank1" & "Rank2" are blank;

2)"Type" is equal to "O" and "Rank1" & "Rank2" are both "17";

3)"Type" is equal to "O" and "Rank1" is blank and "Rank2" is "17" or vice versa.

attached sample raw file for your reference.

Best Regards

QianNing

Re: Show Multiple Fields Content based on a condition

s10157754 — Thu, 24 Jan 2019 12:59:17 GMT

Hi Sunny,

Sorry for not explaining the question clearly.
I just want to display the value from Rank 1 & Rank 2 columns with count of the value as well.
For example:

So my chart would show like this:

Do not display the value if:

1)"Type" is equal to "O" and "Rank1" & "Rank2" are blank;

2)"Type" is equal to "O" and "Rank1" & "Rank2" are both "17";

3)"Type" is equal to "O" and "Rank1" is blank and "Rank2" is "17" or vice versa.

attached sample raw file for your reference.

Best Regards

QianNing

Re: Show Multiple Fields Content based on a condition

sunny_talwar — Thu, 24 Jan 2019 13:04:52 GMT

May be use a CrossTable Load to transform your data while loading it

Table:
CrossTable (Label, Rank)
LOAD Type, 
     Rank1, 
     Rank2
FROM
[..\..\Downloads\Qv.xlsx]
(ooxml, embedded labels, table is Sheet1);

Once you do this... try this

Dimension

Rank

Expression

=Count({<Rank -= {'17'}>}Rank)

Re: Show Multiple Fields Content based on a condition

s10157754 — Thu, 24 Jan 2019 13:24:39 GMT

Hi Sunny,

Thank you for your solution, as my database already built a lot of charts by using the same set of raw data, if i change my loading script to crosstable, it will affect all my other charts as well. Is there any way we can get the same outcome ^ by modifying the equation in the expression only? (Assume that I only use normal loading)

Best Regards
QianNing

Re: Show Multiple Fields Content based on a condition

sunny_talwar — Thu, 24 Jan 2019 13:33:23 GMT

You would need to make some modifications in the script... I have added an island table which won't impact anything else in your dashboard

Table:
LOAD Type, 
     Rank1, 
     Rank2
FROM
[..\..\Downloads\Qv.xlsx]
(ooxml, embedded labels, table is Sheet1);

RankIsland:
LOAD Distinct Rank1 as Rank
Resident Table;

Concatenate (RankIsland)
LOAD Distinct Rank2 as Rank
Resident Table;

The RankIsland table won't and should not join to anything else in your dashboard....

Once you have this, try this

Dimension

Rank

Expression

=If(Rank <> 17, Count(If(Rank1 = Rank, Type))+Count(If(Rank2 = Rank, Type)))

Re: Show Multiple Fields Content based on a condition

s10157754 — Thu, 24 Jan 2019 13:52:58 GMT

Dear Sunny,

Thank you so much for the help!

Best Regards
QianNing

Re: Show Multiple Fields Content based on a condition

s10157754 — Thu, 24 Jan 2019 15:00:51 GMT

Dear Sunny,

Just to clarify with you, if I want to meet a few more criteria on a specific field, can i just add in simply like this?
=If(Rank <> 17 and Type= 'O', Count(If(Rank1 = Rank, Type))+Count(If(Rank2 = Rank, Type)))

Best Regards
QianNing

Re: Show Multiple Fields Content based on a condition

sunny_talwar — Thu, 24 Jan 2019 15:05:58 GMT

I would do this

=If(Rank <> 17,
  Count(If(Rank1 = Rank and Type= 'O', Type)) +
  Count(If(Rank2 = Rank and Type= 'O', Type))
)

Re: Show Multiple Fields Content based on a condition

s10157754 — Thu, 24 Jan 2019 15:18:31 GMT

Dear Sunny,

Thank you so much for the help! Have a nice day!

Best Regards
QianNing