Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
s10157754
Creator III
Creator III

Show Multiple Fields Content based on a condition

Dear Qlivkiew Experts,

I have this set of database:

1.PNG

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:

3.PNG

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

Attached Qlikview excel file for your reference.

Best Regards

QianNing

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

13 Replies
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

Screenshot_1.jpg

If your row data is like you mentioned:

1.PNG

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
Creator III
Creator III
Author

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
Partner - Specialist III
Partner - Specialist III

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
Creator III
Creator III
Author

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:


O.PNG

So my chart would show like this:

2.PNG

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
Creator III
Creator III
Author

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:


O.PNG

So my chart would show like this:

2.PNG

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)

image.png

s10157754
Creator III
Creator III
Author

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

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