Qlik Sense Aggregate and compare values in two fields

Question

In Qlik Sense (charts only) I need some help to identify and calculate occurrences for each ID where values in CODE_A does not match the values in CODE_B.

Based on my example data, I want a straight table with a row for each ID and the number of "Errors" like this:

For each ID It's an error, if the values in CODE_A don't have a match in CODE_B. In this case :

ID 1 is not an error because the value "AA" also is present in CODE_B.
ID 2 has 1 error because "CC" <> "DD"
ID 5 has 2 errors because neither "AA" or "BB" is in CODE_B
ID 6 has 0 errors because value "CC" is in both CODE_A and CODE_B

Sample data:

Data:
Load *
Inline [ID, CODE_A, CODE_B
1, AA, AA
1, AA, BB
2, CC, DD
3, EE, EE
3, EE, FF
4, FF, FF
4, FF, GG
5, AA, FF
5, BB, GG
6, CC, DD
6, EE, CC
];

Humpelstielzchen · Accepted Answer · 2024-01-24 10:14:21Z

0

A very special concept.. If I get you right:

You want zero errors counted if at least one CODE_A exists in CODE_B for a given ID. Like IDs 1,3,4,6
You want every Code counted in CODE_A if none match with CODE_B for a given ID. Like IDs 2 and 5

Then there are different ways to achieve that.

Backend:

Data: 
Load * Inline [ID, CODE_A, CODE_B 
1, AA, AA 
1, AA, BB 
2, CC, DD 
3, EE, EE 
3, EE, FF 
4, FF, FF 
4, FF, GG 
5, AA, FF 
5, BB, GG 
6, CC, DD 
6, EE, CC ];

Left Join
Load ID, CODE_B as CODE_A, 1 as flag
RESIDENT Data;

Then in frontend:

Straight table with ID and the following formula

Sum(Aggr(If(IsNull(Max(flag)), COUNT(1), 0), ID))

And you get:

I hope that is what you are looking for.

answered Jan 24, 2024 at 10:14

Humpelstielzchen

6,4613 gold badges17 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Simonsen Over a year ago

Very nice, but I have no access to work in the load script. Is it possible to make this in chart expression only.

Collectives™ on Stack Overflow

Qlik Sense Aggregate and compare values in two fields

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related