Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I'd like to share the goal I'm searching for:
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'
];
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
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
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
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
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