Must I apply an intersection or maybe there's a better way?

[LDR]

Hello everyone,

I'd like to share the goal I'm searching for:

• Data Model:                              

                                

• Data:

Operations:
LOAD
*
Inline [
Date,Part,Operation,Id,Machine,NOKReason
'2021-09-17','AAAA','Operation1','000001','M1.1',
'2021-09-17','AAAB','Operation1','000002','M1.2',
'2021-09-17','AAAC','Operation1','000003','M1.1',
'2021-09-17','AAAA','Operation1','000004','M1.3',
'2021-09-17','AAAB','Operation1','000005','M1.2',
'2021-09-17','AAAC','Operation1','000006','M1.3',
'2021-09-17','AAAA','Operation2','000001','M2.1',
'2021-09-17','AAAB','Operation2','000002','M2.1','R1'
'2021-09-17','AAAC','Operation2','000003','M2.2','R1'
'2021-09-17','AAAA','Operation2','000005','M2.1',
'2021-09-18','AAAB','Operation2','000006','M2.2',
'2021-09-18','AAAC','Operation1','000007','M1.3',
'2021-09-17','AAAA','Operation3','000001','M3.1',
'2021-09-17','AAAB','Operation3','000002','M3.1',
'2021-09-17','AAAC','Operation3','000003','M3.1',
'2021-09-19','AAAA','Operation3','000005','M3.1','R2'
'2021-09-19','AAAB','Operation3','000006','M3.1','R2'
];

NOKReasons:
LOAD *
Inline [
NOKReason,Operation_Source
'R1','Operation1'
'R2','Operation2'
];

• Tables description: 
  • Operations: include one record for each "Id" and "Operation". Imagine you have a production line and in order to obtain the finish product each product must be processed from Operation 1 to 3. Each product is identified by the column "Id". If for some reason we detect that any "Id" is NOK we fullfil the column "NOKReason".
  • NOKReasons: contains the operation where the fail was originated. This information is included in column "Operation_Source".
• Goal: I want to obtain al "Ids" where <NOK_Source={'Operation1'}> and <[Operation]={'Operation1'}>. Why I want that? because I need to know the date when these NOK Ids were processed for Operation1, and also the machine where they were processed, etc.. You know, we have noticed the error downstream and because we didn't detect it before it was an extra-cost for us.
• How have I solved?
  • NOK Ids with Operation_Source = 'Operation1':

I want to know the ID , the date when they pass for the "Operation1" and the machine where it was processed.

The solution I applied works for me till I select some Machine in the table. Let me show you:

So, my question is: if I want to be able to apply filters by some of the previous columns, what I have to do????

I would solve it very fast using SQL but know I have to learn the Qlik way 😅

Thanks a lot for your support.

Regards

[micheledenardi · Accepted solution]

Good morining,

since your tables are connected via "NOKReason" Qlik is using that field as key,  so if you try to count "Ids" where <NOK_Source={'Operation1'}> and <[Operation]={'Operation1'}> the result will be 0.

In this case the expression will be:

Count(Distinct {<Operation={'Operation1'},NOK_Source={'Operation_Source'}>}Id)

As soon as you select make selection on your calculated dimension "Machine" (calculated dimensions are not  best practice) the result will be 0 again for the same reason, there are no records on Operations connected with a NOKReason record.

Using the expression you wrote the result is wrong because as you can see, NOKReason is empty, this will cause a missing key to go on NOKReason table, so you'll not achieve your requirements:

You cannot make selections on previous columns, since you're using an union on your set analysis and the key you're using is not consistent.

My approach to this issue probably would be to make your dataset consistent, fill the missing NOKReason in your Operation table, then use the expression i wrote. 

Check this solution and let me know:

Operations:
LOAD
	*,
    Part as [%Key Part]
Inline [
Date,Part,Operation,Id,Machine,NOKReasonOperation
2021-09-17,AAAA,Operation1,000001,M1.1,
2021-09-17,AAAB,Operation1,000002,M1.2,
2021-09-17,AAAC,Operation1,000003,M1.1,
2021-09-17,AAAA,Operation1,000004,M1.3,
2021-09-17,AAAB,Operation1,000005,M1.2,
2021-09-17,AAAC,Operation1,000006,M1.3,
2021-09-17,AAAA,Operation2,000001,M2.1,
2021-09-17,AAAB,Operation2,000002,M2.1,R1
2021-09-17,AAAC,Operation2,000003,M2.2,R1
2021-09-17,AAAA,Operation2,000005,M2.1,
2021-09-18,AAAB,Operation2,000006,M2.2,
2021-09-18,AAAC,Operation1,000007,M1.3,
2021-09-17,AAAA,Operation3,000001,M3.1,
2021-09-17,AAAB,Operation3,000002,M3.1,
2021-09-17,AAAC,Operation3,000003,M3.1,
2021-09-19,AAAA,Operation3,000005,M3.1,R2
2021-09-19,AAAB,Operation3,000006,M3.1,R2
];

NOKReasons:
LOAD *
Inline [
NOKReason,NOK_Source
R1,Operation1
R2,Operation2
];

MidTable:
Load Distinct
	[%Key Part],
    NOKReasonOperation as NOKReason
Resident Operations
	Where not isnull(NOKReasonOperation) And trim(NOKReasonOperation)<>'';

Then create a normal table with plain dimensions and the formula i wrote

[LDR]

Hi Micheledenardi ,

First at all thanks for your answer.

Ok, yes I totally agree with you regarding what is happening so according your experience the best way is completing my dataset via ETL. I thought it would be possible using formulas in the front-end.

You know, according my data model and applying some easy SQL sentence it would be solved like that:

SELECT
ID,
Date,
Operation,
Machine,
SUM(Qty)
FROM Operations
WHERE ID IN (SELECT ID FROM Operations WHERE NOKReason = 'R1')
AND Operation = 'Operation1'
GROUP BY ID,Date,Operation,Machine;

But you're right I'm applying filters that are affecting my dataset (I knew that 😔) ... anyway I will try to see how to transfor the data I need.

I will send you my feedback when it will be solved.

Thanks

[LDR]

Finally I solved it modifying my dataset:

Operations:
LOAD
*
Inline [
RowNo,Date,Part,Operation,Id,Machine,NOKReason
1,'2021-09-17','AAAA','Operation1','000001','M1.1',
2,'2021-09-17','AAAB','Operation1','000002','M1.2',
3,'2021-09-17','AAAC','Operation1','000003','M1.1',
4,'2021-09-17','AAAA','Operation1','000004','M1.3',
5,'2021-09-17','AAAB','Operation1','000005','M1.2',
6,'2021-09-17','AAAC','Operation1','000006','M1.3',
7,'2021-09-17','AAAA','Operation2','000001','M2.1',
8,'2021-09-17','AAAB','Operation2','000002','M2.1','R1'
9,'2021-09-17','AAAC','Operation2','000003','M2.2','R1'
10,'2021-09-17','AAAA','Operation2','000005','M2.1',
11,'2021-09-18','AAAB','Operation2','000006','M2.2',
12,'2021-09-18','AAAC','Operation1','000007','M1.3',
13,'2021-09-17','AAAA','Operation3','000001','M3.1',
14,'2021-09-17','AAAB','Operation3','000002','M3.1',
15,'2021-09-17','AAAC','Operation3','000003','M3.1',
16,'2021-09-19','AAAA','Operation3','000005','M3.1','R2'
17,'2021-09-19','AAAB','Operation3','000006','M3.1','R2'
];

LEFT JOIN (Operations)
LOAD *
Inline [
NOKReason,ReasonDescription,Operation_Source
'R1','Description 1','Operation1'
'R2','Description 2','Operation2'
];

LEFT JOIN (Operations)
LOAD
Id,
Operation_Source AS [Operation],
Operation AS [Operation NOK Detected],
[Date] AS [Operation NOK Date],
[Machine] AS [Operation NOK Machine]
RESIDENT Operations
WHERE NOKReason <> NULL() ;

Now I'm obtaining the same result than at the beginning and I can filter by machine without losing any row.

Thanks Micheledenardi , sometimes you don't need to know how to do it else how  don't do it