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. 🙂
