Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
We value your feedback – take our 5-minute QlikView modernization survey
cancel
Showing results for 
Search instead for 
Did you mean: 
citizenk
Contributor III
Contributor III

conditional colour with substringcount not working properly in pivot table

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.

substringcount colour.png

 

 

 

 

 

 

 

 

 

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.

Labels (3)
1 Solution

Accepted Solutions
citizenk
Contributor III
Contributor III
Author

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 dimensionsolution - colour the expression, not the dimension

View solution in original post

5 Replies
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

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

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:

substringcount colour_1.png

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

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

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
citizenk
Contributor III
Contributor III
Author

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 dimensionsolution - colour the expression, not the dimension