conditional colour with substringcount not working properly in pivot table

[citizenk]

Dear all,

I am struggling with a scenario I faced several times.

Based on a comparison I perform with a substringcount want to set the background of a cell. In a straight table this works, however when I have a pivot table not all the cells I expect are coloured.

In this example I want to compare the values in the column "ID" of the left table with the values of the column ID in the right table.

Whenever there is a ID in the right table that is not in the left table it should get the background colour red.

As you can see some IDs are coloured while I would expect that all Porsche Cayenne would be red since none of them appears in the left table.

The expression I used for setting the background colour of the column cells is:

=if(SubStringCount(Concat(distinct LEFT_TABLE_ID, '|' ), RIGHT_TABLE_ID),white(),lightred())

I already tried implementing some aggregations around some parts of this expression but nothing really worked out yet.

Switching the table style to "straight table" everything turns out to be coloured correctly.

[Stoyan_Terziev]

Hello CitizenK,

The substring function will work just fine. The problem may be in the LEFT TABLE IDs.

The left one looks like a table chart (not a regular table object). So I suspect there is a measure to it which may be hidden or cropped out of the picture. (if you create a list box with the Left-table-ID field you will see all values you truly pick in your Concat function)

What you see on the front end is not the entire set of "Left Table ID"s but just the ones that generate some non-zero value of the hidden expression.

I think the solution will look like this:

=if(SubStringCount(Concat({<HiddenMeasureField-={0}>} distinct LEFT_TABLE_ID, '|' ), RIGHT_TABLE_ID),white(),lightred())

However, you may need to consider an Aggr() function to account for all other dimensions in the table chart to the left.

I hope this helps!

Kind regards,

S.T.

[citizenk]

Hi Stoyan,

Thanks for this super quick reply.

Actually the left table is only designed as a chart for the purpose of showing it in the pivot style. There is no calculation or measurement about it apart from hiding the null values for ID.

I recreated it as a table box and a listbox which return the same values of the left table:

This leads to the assumption that an aggregation has to be implemented in the right table colour condition.

Actually the left table represents the following values =AGGR(ID, PBGA_MASTER_ID).
Now I am wondering how to apply this to the expression - if that would solve the problem.

[Stoyan_Terziev]

Hi CitizenK,

I think few clarifications may be needed here. But quick solution may be in place.

Can you try this:

=IF(SubStringCount(
'-'&concat({<LEFT_TABLE_ID={"*"}>}DISTINCT LEFT_TABLE_ID, '-')&'-',
'-'&RIGHT_TABLE_ID&'-'
), White(),LightRed()
)

And if it doesn't work - we need to collect some more details.

Regards,
S.T.

[Brett_Bleess]

Ringo, did Stoyan's last post get you what you needed to get a working solution?  If so, please be sure to return to the thread and use the Accept as Solution button on his post to give him credit and let other Community members know it did work.  Regarding the AGGR side of things, Do a search on AGGR in the following link and check out those posts, as they may be helpful in your use case, was not sure which one(s) might be most helpful.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

[citizenk · Accepted solution]

The solution (even if it is a workaround womehow:

I stopped forcing the dimension to colour the gaps and instead coloured the expressions which works like a charm for the Pivot table.

solution - colour the expression, not the dimension