Get id for specific set of rules

[qlicky]

Hi,

I'am new to QLIK and learned a lot from this forum. But now i have a problem which i could not solve myself.

Given the following table I need a formula to find a single error_date for each part_id if the following rules are met:

1. For every part_id, find the the earliest error_date for which error_check is 0.

2. If there are 2 matching elements (like in part_id = 2 in the example) chose one row (randomly). In this case the categorys are the same anyways.

After that, aggregate over the result and count the frequency of each of the 3 category A,B and C. 

part_id	error_date	error_check	category
1	11.01.2021	0	B
1	10.01.2021	1	C
2	10.02.2021	1	A
2	01.02.2021	0	C
2	01.02.2021	0	C
3	01.02.2021	0	B
3	21.02.2021	0	A
4	01.01.2021	0	A
5	01.01.2021	1	A

The result should be:

part_id	error_date	error_check	category	count_cat_A	count_cat_b	count_cat_C
				sum = 1	sum = 2	sum = 1
1	11.01.2021	0	B	0	1	0
2	01.02.2021	0	C	0	0	1
3	01.02.2021	0	B	0	1	0
4	01.01.2021	0	A	1	0	0

What i got so far is a formula for each category:

Sum(aggr(if(count({$<category={'A'}>} [part_id])>0 and Min(error_check)=0,1,0), part_id))

But i don't know how to handle the problem with the earliest date.

Any help is appreciated. 🙂

[fernando_tonial · Accepted solution]

You can see the qvf file.

Best Regards.
Tonial.

[qlicky]

Thanks for the solution. Works like a charm.  

Unfortunately i could not open the *.qvf file because I don’t have the rights to do so in our Qlik-Sense environment. But your Screenshot gave me the much needed hints.

So again, thanks!