How to merge multiple rows in just one? Avoding Script Editor

[LDR]

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:

• STEP1:
  • SUM({<STEP={'STEP1'}>}QTY)
• STEP2:
  • SUM({<STEP={'STEP2'}>}QTY)

And dimensions MACHINE_STEP1 and MACHINE_STEP2 would be obtained using these other formulas:

• MACHINE_STEP1:
  • IF(STEP='STEP1',MACHINE,Null())
• MACHINE_STEP2:
  • IF(STEP='STEP2',MACHINE,Null())

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

[rwunderlich · Accepted solution]

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

[edwin · Accepted solution]

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

[LDR]

Thanks Edwin

[LDR]

Thanks rwunderlich