Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
LDR
Creator II
Creator II

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

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:

TSIDSTEPMACHINEQTY
22/03/20210191231564STEP1M1.11
22/03/20210191231564STEP2M2.11
27/03/20210191231565STEP1M1.21
27/03/20210191231565STEP2M2.31

 

And the result I want to show using a table chart is the next below:

IDSTEP1MACHINE_STEP1STEP2MACHINE_STEP2
01912315641M1.11M2.1
01912315651M1.21M2.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

Labels (1)
2 Solutions

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

edwin
Master II
Master II

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

View solution in original post

4 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Master II
Master II

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
Creator II
Creator II
Author

Thanks Edwin

LDR
Creator II
Creator II
Author

Thanks rwunderlich