topic Re: Displaying distinct rows based on two columns in App Development

Displaying distinct rows based on two columns

MarcelPadu — Fri, 27 Jan 2023 14:30:38 GMT

Im trying to display rows from the table underneath but only when i have 2 or more C1 fields with the same value but different C2 values.

In this case:

Changes:
Load * inline [
C1 C2
1 1
2 3
2 3
2 4
3 5
3 2
4 12
4 12
6 10
] (delimiter is '\t');

i should only display

2 3

2 4

5 3

5 2

i found that using

=if(Aggr(Count(C1),C1)<>Aggr(Count(C2),C2),C1)

helps me find the fields fast but it only displays 1 value of C2 and i have to check another table to see the values.

Oleg_Troyansky — Fri, 27 Jan 2023 16:39:41 GMT

Hi there!

I'd aggregate the data in the script and calculate a conditional flag that determines if the data should be displayed or not:

ChangesFlag:

LOAD

C1,

IF(C2_Count > 1, 1, 0) as ChangeFlag

;

LOAD

C1,

count(C2) as C2_Count

;

LOAD DISTINCT

C1, C2, 1 as NoconcatDummy

RESIDENT

Changes

;

Something like this.

Come to the virtual Masters Summit for Qlik to learn advanced techniques like this one, and many others!

jbhappysocks — Mon, 30 Jan 2023 10:13:29 GMT

=aggr(
if(count(distinct total <C1> C2)>1, C1)
,C2,C1)

Change bold to C2 for 2:nd column

MarcelPadu — Mon, 30 Jan 2023 10:54:28 GMT

Yeah it works perfectly, many thanks!