topic Re: Highlight duplicate rows in QlikView

Highlight duplicate rows

kinjal1645 — Wed, 25 Nov 2020 16:16:04 GMT

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.

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)

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

Thank you,

Kinjal

Re: Highlight duplicate rows

vishsaggi — Wed, 20 Jun 2018 01:33:34 GMT

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

Re: Highlight duplicate rows

kinjal1645 — Wed, 20 Jun 2018 14:28:39 GMT

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

Re: Highlight duplicate rows

vishsaggi — Wed, 20 Jun 2018 14:53:42 GMT

Hmmm. Not sure if that is possible. May be lets hear from stalwar1‌ marcowedel‌

Re: Highlight duplicate rows

sunny_talwar — Thu, 21 Jun 2018 01:22:57 GMT

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

Re: Highlight duplicate rows

kinjal1645 — Fri, 22 Jun 2018 14:52:19 GMT

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

Re: Highlight duplicate rows

vishsaggi — Fri, 22 Jun 2018 15:17:47 GMT

Hello Kinjal,

Did you read Sunny's response posted earlier?

Re: Highlight duplicate rows

sunny_talwar — Fri, 22 Jun 2018 15:34:07 GMT

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+

Re: Highlight duplicate rows

kinjal1645 — Fri, 22 Jun 2018 17:20:33 GMT

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)

Thanks in advance

Re: Highlight duplicate rows

sunny_talwar — Fri, 22 Jun 2018 17:28:23 GMT

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?

Re: Highlight duplicate rows

kinjal1645 — Fri, 22 Jun 2018 17:35:11 GMT

Yes, I want to find distinctness. But I am renaming values in one of the parameters in the original app Eg: HONG_KONG, HONGKONG to HONG KONG through calculated dimension.

Re: Highlight duplicate rows

sunny_talwar — Fri, 22 Jun 2018 17:40:18 GMT

Do this in the script...

Re: Highlight duplicate rows

kinjal1645 — Wed, 27 Jun 2018 22:10:53 GMT

Hi stalwar1‌,

I tried this with actual data (10,000+ rows) and duplicate rows were getting highlighted correctly but few unique rows were getting highlighted too.

I am trying to figure out what is wrong but can't find it . I have highlighted one such case:

In attached qvw I am trying to highlight duplicates for combination of parameter2 to paramter9.

It would be great if you can help

Thanks,

Kinjal

Re: Highlight duplicate rows

sunny_talwar — Thu, 28 Jun 2018 10:59:40 GMT

I am not sure why, but I am seeing two rows for the last row in the chart

And the Excel you have attached seem to only have 37 rows, whereas the dashboard was reloaded with 360 rows of data... so I can't really see the raw data to know if the data that was reloaded had duplicate or not.

Best,

Sunny

Re: Highlight duplicate rows

kinjal1645 — Thu, 28 Jun 2018 15:24:04 GMT

Hi Sunny,

I had reloaded same excel that was attached.

But I have now done inline load to clear the confusion. However still getting same output.

Re: Highlight duplicate rows

sunny_talwar — Thu, 28 Jun 2018 15:29:23 GMT

I am not sure I understand your issue because I see two rows in your inline load. Can you tell me why are those not duplicates?

Re: Highlight duplicate rows

kinjal1645 — Thu, 28 Jun 2018 15:47:10 GMT

Thank you again

I never analyzed the source data for the problem

Re: Highlight duplicate rows

sunny_talwar — Thu, 28 Jun 2018 15:49:15 GMT

That is usually the first place to look... you might think there are no duplicates, but unless you check the raw data... how can you confirm, right? Keep that in mind for the future my friend

Re: Highlight duplicate rows

kinjal1645 — Thu, 28 Jun 2018 15:51:22 GMT

Right