Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
kinjal1645
Creator
Creator

Highlight duplicate rows

Hello,

I want to highlight duplicate rows i.e rows with similar parameters.

Currently, I have added last column which concatenates parameters and highlights duplicates.

Capture.GIF

What is required:

1. Highlight entire row for duplicate parameters? (not by adding background color in all dimensions of a table)

2. It should work if any new parameter is added. So if parameter4 is added in above chart, and there are duplicate rows including pararmeter4 then it should be automatically highlighted without any code change (actual application has more than 30 parameters and this number is not constant so don't want to waste efforts on maintenance)

Capture.GIF

Let me know how to achieve this - approach can be different then existing one (but without data model change)

Thank you,

Kinjal

1 Solution

Accepted Solutions
sunny_talwar

This is the best you can do...

create a variable like this

If(Aggr(NODISTINCT

Count(

$(=If(IsNull(GetObjectField(1, 'CH01')), 1, GetObjectField(1, 'CH01')))&

$(=If(IsNull(GetObjectField(2, 'CH01')), 1, GetObjectField(2, 'CH01')))&

$(=If(IsNull(GetObjectField(3, 'CH01')), 1, GetObjectField(3, 'CH01')))&

$(=If(IsNull(GetObjectField(4, 'CH01')), 1, GetObjectField(4, 'CH01')))&

$(=If(IsNull(GetObjectField(5, 'CH01')), 1, GetObjectField(5, 'CH01')))&

$(=If(IsNull(GetObjectField(6, 'CH01')), 1, GetObjectField(6, 'CH01')))&

$(=If(IsNull(GetObjectField(7, 'CH01')), 1, GetObjectField(7, 'CH01')))&

$(=If(IsNull(GetObjectField(8, 'CH01')), 1, GetObjectField(8, 'CH01')))&

$(=If(IsNull(GetObjectField(9, 'CH01')), 1, GetObjectField(9, 'CH01')))&

$(=If(IsNull(GetObjectField(10, 'CH01')), 1, GetObjectField(10, 'CH01')))&

$(=If(IsNull(GetObjectField(11, 'CH01')), 1, GetObjectField(11, 'CH01')))&

$(=If(IsNull(GetObjectField(12, 'CH01')), 1, GetObjectField(12, 'CH01')))&

$(=If(IsNull(GetObjectField(13, 'CH01')), 1, GetObjectField(13, 'CH01')))&

$(=If(IsNull(GetObjectField(14, 'CH01')), 1, GetObjectField(14, 'CH01')))&

$(=If(IsNull(GetObjectField(15, 'CH01')), 1, GetObjectField(15, 'CH01')))&

$(=If(IsNull(GetObjectField(16, 'CH01')), 1, GetObjectField(16, 'CH01')))&

$(=If(IsNull(GetObjectField(17, 'CH01')), 1, GetObjectField(17, 'CH01')))&

$(=If(IsNull(GetObjectField(18, 'CH01')), 1, GetObjectField(18, 'CH01')))&

$(=If(IsNull(GetObjectField(19, 'CH01')), 1, GetObjectField(19, 'CH01')))&

$(=If(IsNull(GetObjectField(20, 'CH01')), 1, GetObjectField(20, 'CH01')))

),


$(=If(IsNull(GetObjectField(1, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(1, 'CH01'))),

$(=If(IsNull(GetObjectField(2, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(2, 'CH01'))),

$(=If(IsNull(GetObjectField(3, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(3, 'CH01'))),

$(=If(IsNull(GetObjectField(4, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(4, 'CH01'))),

$(=If(IsNull(GetObjectField(5, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(5, 'CH01'))),

$(=If(IsNull(GetObjectField(6, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(6, 'CH01'))),

$(=If(IsNull(GetObjectField(7, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(7, 'CH01'))),

$(=If(IsNull(GetObjectField(8, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(8, 'CH01'))),

$(=If(IsNull(GetObjectField(9, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(9, 'CH01'))),

$(=If(IsNull(GetObjectField(10, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(10, 'CH01'))),

$(=If(IsNull(GetObjectField(11, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(11, 'CH01'))),

$(=If(IsNull(GetObjectField(12, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(12, 'CH01'))),

$(=If(IsNull(GetObjectField(13, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(13, 'CH01'))),

$(=If(IsNull(GetObjectField(14, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(14, 'CH01'))),

$(=If(IsNull(GetObjectField(15, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(15, 'CH01'))),

$(=If(IsNull(GetObjectField(16, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(16, 'CH01'))),

$(=If(IsNull(GetObjectField(17, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(17, 'CH01'))),

$(=If(IsNull(GetObjectField(18, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(18, 'CH01'))),

$(=If(IsNull(GetObjectField(19, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(19, 'CH01'))),

$(=If(IsNull(GetObjectField(20, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(20, 'CH01')))


) > 1, LightCyan())

All all you would need to do is to add the variable in Background color expression every time you add a new paramter for upto 20... For more than 20... just update the variable to 21+

View solution in original post

18 Replies
vishsaggi
Champion III
Champion III

Just use the same background formula in your dimension background. Check attached.

kinjal1645
Creator
Creator
Author

I want to highlight entire row in one go. So if any new dimension is added in the table, it should be automatically highlighted if it is in duplicate rows.

Let me know if this is possible

vishsaggi
Champion III
Champion III

Hmmm. Not sure if that is possible. May be lets hear from stalwar1marcowedel

sunny_talwar

I don't think think this can be fully automized, but partial automation can happen. The first and foremost somebody needs to add an additional dimension to the chart and while this is added, why can't they quickly add an expression to the background of the chart or have you somehow automized a way to add a parameter?

I have not looked at the qvw file yet, but will def. give a quick look sometime tomorrow, but again no matter if you have 30 or 300 parameter, as long as your are going to add manually add one additional parameter to your chart, why can't you just add the background expression as well?

Best,

Sunny

kinjal1645
Creator
Creator
Author

Hi stalwar1

Did you get chance to look at the file?

Following expression is being used:

=if (aggr(NODISTINCT count(parameter1&parameter2&parameter3),parameter1,parameter2,parameter3)>1,LightCyan())

So if any new parameter is added, background expression of all existing dimensions needs to be updated as well

Let me know if this can be modified.

Thanks,

Kinjal

vishsaggi
Champion III
Champion III

Hello Kinjal,

Did you read Sunny's response posted earlier?

sunny_talwar

This is the best you can do...

create a variable like this

If(Aggr(NODISTINCT

Count(

$(=If(IsNull(GetObjectField(1, 'CH01')), 1, GetObjectField(1, 'CH01')))&

$(=If(IsNull(GetObjectField(2, 'CH01')), 1, GetObjectField(2, 'CH01')))&

$(=If(IsNull(GetObjectField(3, 'CH01')), 1, GetObjectField(3, 'CH01')))&

$(=If(IsNull(GetObjectField(4, 'CH01')), 1, GetObjectField(4, 'CH01')))&

$(=If(IsNull(GetObjectField(5, 'CH01')), 1, GetObjectField(5, 'CH01')))&

$(=If(IsNull(GetObjectField(6, 'CH01')), 1, GetObjectField(6, 'CH01')))&

$(=If(IsNull(GetObjectField(7, 'CH01')), 1, GetObjectField(7, 'CH01')))&

$(=If(IsNull(GetObjectField(8, 'CH01')), 1, GetObjectField(8, 'CH01')))&

$(=If(IsNull(GetObjectField(9, 'CH01')), 1, GetObjectField(9, 'CH01')))&

$(=If(IsNull(GetObjectField(10, 'CH01')), 1, GetObjectField(10, 'CH01')))&

$(=If(IsNull(GetObjectField(11, 'CH01')), 1, GetObjectField(11, 'CH01')))&

$(=If(IsNull(GetObjectField(12, 'CH01')), 1, GetObjectField(12, 'CH01')))&

$(=If(IsNull(GetObjectField(13, 'CH01')), 1, GetObjectField(13, 'CH01')))&

$(=If(IsNull(GetObjectField(14, 'CH01')), 1, GetObjectField(14, 'CH01')))&

$(=If(IsNull(GetObjectField(15, 'CH01')), 1, GetObjectField(15, 'CH01')))&

$(=If(IsNull(GetObjectField(16, 'CH01')), 1, GetObjectField(16, 'CH01')))&

$(=If(IsNull(GetObjectField(17, 'CH01')), 1, GetObjectField(17, 'CH01')))&

$(=If(IsNull(GetObjectField(18, 'CH01')), 1, GetObjectField(18, 'CH01')))&

$(=If(IsNull(GetObjectField(19, 'CH01')), 1, GetObjectField(19, 'CH01')))&

$(=If(IsNull(GetObjectField(20, 'CH01')), 1, GetObjectField(20, 'CH01')))

),


$(=If(IsNull(GetObjectField(1, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(1, 'CH01'))),

$(=If(IsNull(GetObjectField(2, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(2, 'CH01'))),

$(=If(IsNull(GetObjectField(3, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(3, 'CH01'))),

$(=If(IsNull(GetObjectField(4, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(4, 'CH01'))),

$(=If(IsNull(GetObjectField(5, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(5, 'CH01'))),

$(=If(IsNull(GetObjectField(6, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(6, 'CH01'))),

$(=If(IsNull(GetObjectField(7, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(7, 'CH01'))),

$(=If(IsNull(GetObjectField(8, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(8, 'CH01'))),

$(=If(IsNull(GetObjectField(9, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(9, 'CH01'))),

$(=If(IsNull(GetObjectField(10, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(10, 'CH01'))),

$(=If(IsNull(GetObjectField(11, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(11, 'CH01'))),

$(=If(IsNull(GetObjectField(12, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(12, 'CH01'))),

$(=If(IsNull(GetObjectField(13, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(13, 'CH01'))),

$(=If(IsNull(GetObjectField(14, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(14, 'CH01'))),

$(=If(IsNull(GetObjectField(15, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(15, 'CH01'))),

$(=If(IsNull(GetObjectField(16, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(16, 'CH01'))),

$(=If(IsNull(GetObjectField(17, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(17, 'CH01'))),

$(=If(IsNull(GetObjectField(18, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(18, 'CH01'))),

$(=If(IsNull(GetObjectField(19, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(19, 'CH01'))),

$(=If(IsNull(GetObjectField(20, 'CH01')), GetObjectField(1, 'CH01'), GetObjectField(20, 'CH01')))


) > 1, LightCyan())

All all you would need to do is to add the variable in Background color expression every time you add a new paramter for upto 20... For more than 20... just update the variable to 21+

View solution in original post

kinjal1645
Creator
Creator
Author

Thanks stalwar1

I have a follow-up question: How to handle this when there are calculated dimensions? (I have few in my original application, this is not working there because there are calculated dimensions)

Capture.GIF

Thanks in advance

sunny_talwar

calculated dimensions don't really work in the Aggr() function.... also, you still want to check the distinctness of parameter, right? not the calculated dimension?