Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I'm writing you because I don't know how to do it unless I use the script editor so my question is how to do it using formulas.
Ok, that's the table I have with multiple rows for the same ID:
TS | ID | STEP | MACHINE | QTY |
22/03/2021 | 0191231564 | STEP1 | M1.1 | 1 |
22/03/2021 | 0191231564 | STEP2 | M2.1 | 1 |
27/03/2021 | 0191231565 | STEP1 | M1.2 | 1 |
27/03/2021 | 0191231565 | STEP2 | M2.3 | 1 |
And the result I want to show using a table chart is the next below:
ID | STEP1 | MACHINE_STEP1 | STEP2 | MACHINE_STEP2 |
0191231564 | 1 | M1.1 | 1 | M2.1 |
0191231565 | 1 | M1.2 | 1 | M2.3 |
Measures STEP1 and STEP2 would be calculated using these formulas:
And dimensions MACHINE_STEP1 and MACHINE_STEP2 would be obtained using these other formulas:
Like I said I know how to do it doing LEFT JOINS in the Script Editor but I'd like to know how to do it using formulas in the front-end. Of course, if it's possible.
Thanks in advance for your help
You just about have it. Except you should use an aggregation rather than if() to get MACHINE_STEPn
Only({<STEP={'STEP1'}>}MACHINE)
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com
so it sounds like you have the step qty figured out.
as to the machine, you will still need an aggregation and what you use will depend:
1. if there can only be one machine per ID per date per step, you can use
only({<STEP={'STEP1'}>}Machine)
only({<STEP={'STEP2'}>}Machine)
2. if it is possible to have multiple rows/machines :
concat(distinct {<STEP={'STEP1'}>}Machine)
concat( distinct {<STEP={'STEP2'}>}Machine)
hope that helps
You just about have it. Except you should use an aggregation rather than if() to get MACHINE_STEPn
Only({<STEP={'STEP1'}>}MACHINE)
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com
so it sounds like you have the step qty figured out.
as to the machine, you will still need an aggregation and what you use will depend:
1. if there can only be one machine per ID per date per step, you can use
only({<STEP={'STEP1'}>}Machine)
only({<STEP={'STEP2'}>}Machine)
2. if it is possible to have multiple rows/machines :
concat(distinct {<STEP={'STEP1'}>}Machine)
concat( distinct {<STEP={'STEP2'}>}Machine)
hope that helps
Thanks Edwin
Thanks rwunderlich