Show Multiple Fields Content based on a condition

[s10157754]

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

[MindaugasBacius]

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);

[s10157754]

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

[MindaugasBacius]

Could you then provide raw data sample?

[sunny_talwar]

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

[s10157754]

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 

[s10157754]

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 

[sunny_talwar]

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)

[s10157754]

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

[sunny_talwar · Accepted solution]

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)))