Show data based on condition in table chart

[Qlik1_User1]

Hi All,

Please help on the below requirement.

Display data only if column 1,2,3 has values, plus country which has max values should come first.

Case 1:

Data

		Data
Country	1	2	3	sales
A	n	n	n	100
b	n	n	v	200
c	v	v	v	300
d	v	v	n	400
e	n	v	v	500

Expected Output

		Output
Country	1	2	3
c	v	v	v	as c country has max v's i.e. 3
e	n	v	v	as e and d country has 2 v but e has more sales so coming before d
d	v	v	n	as e and d country has 2 v but e has more sales so coming after e
b	n	n	v	as b country has 1 v

Case 2:

Country	1	2	3
A	n	n	n
b	n	n	n
c	n	n	n
d	n	n	n
e	n	n	n

Expected output : Table should be blank.

[Andrei_Cusnir · Accepted solution]

Hello,

Here are some steps that helped me achieve the use case scenario that you are looking for:

1. I have loaded the dataset with the values that you have provided:

2. I have created a new Straight table:

• Added Country, Data1, Data2 and Data3 as dimension
• Added dimension expression: =If(IsNull(Data1) AND IsNull(Data2) AND IsNull(Data3), Null(), Sales)

This expression checks if the value is Null for Data1 AND Data2 AND Data3, so in that case it will return Null(), otherwise, it will return the Sales value for that row. Un-checking the "Include null values" will remove that row entirely from the table.

Now we need to check the sorting strategy:

• Go to Table chart configuration > Sorting
• Move the last entry (Which is the expression that you added), to the top of the list. So it first will sort based on the expression and then based on the rest of dimensions.
• Expand the options for the expression:
  • Check "Sort by expression" and choose "Descending"
  • For expression use: =Count(Data1) + Count(Data2) + Count(Data3)
  • Then make sure that:
    • Sort numerical is checked and "Descending" is chosen
    • Sort alphabetically us checked and "Descending" is chosen

The options above will first sort based on how many values are found among Data1, Data2 and Date3, and then it will move forward.

So the outcome with data is:

And without data is:

As you have noticed it behaves the same way with the examples that you have provided!

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members.