If = multiple values, merge in to 1 value in new dimension (group by)

[davyqliks]

Hello,

i hope you can assist.

I have a dimension [Part] with individual values which i need to merge into a single value if meeting 2 or more of the values within a [Portal Order No.]

this is my attempt,

Load
*,
IF(Part = 'Shell Fabric', 'ShellFabric',
IF(Part= 'Lining', 'Lining',
IF(Part = 'Padding', 'Padding',
IF(Part = 'Shell Fabric'and 'Lining', 'ShellFabric-Lining',
IF(Part= 'Shell Fabric'and 'Padding', 'ShellFabric-Padding',
IF(Part= 'Padding'and 'Lining', 'Padding-Lining',
IF(Part= 'Shell Fabric'and 'Lining', 'Padding', 'ShellFabric-Lining-Padding'

))))))) AS combined

from
[lib://Qlik Sense E Drive/Live/QVD's/AllData.QVD](QVD)
Group by "Portal Order No."
;

But i get the following error

I need to only match when in the same portal order no.

e.g. Portal order no = 1234 and has lining and padding associated to it, i need

345 has padding only

678 has padding and lining

portal order no	combined
1234	liningpadding
345	Padding
678	Paddinglining

is it possible to match combinations like lining and padding and not capture just the single values and group by another dimension?

thank you in advance for any assistance.

Daniel

[Taoufiq_Zarra · Accepted solution]

@davyqliks  if I understood correctly you need to use concat() function

for exemple if you have this input:

portal order no	Part
1234	Padding
1234	Lining
345	Padding
678	Padding
678	Lining

you can use this script:

LOAD [portal order no],concat(Part,'-') as Part INLINE [
    portal order no, Part 
    1234, Padding
    1234, Lining
    345, Padding
    678, Padding
    678, Lining
] group by [portal order no];

output:

[davyqliks]

Hi,

Thanks so much,

I see where this is going however i have a little trouble getting it to perform correctly when i do not inline load.

LOAD "Portal Order No.",concat(Part,'-') as Partcombined

from
[lib://Qlik Sense E Drive/Live/QVD's/AllData.QVD](QVD)
group by "Portal Order No."
;

The above attempt at converting to my load  is giving me repetition, can you help to correct this please?

i would expect to see Lining-Shell Fabric on both lines here .

thank you so much for your fast response,

Kind regards,

[Taoufiq_Zarra · Accepted solution]

you can updated by

LOAD "Portal Order No.",concat(distinct Part,'-') as Partcombined

from
[lib://Qlik Sense E Drive/Live/QVD's/AllData.QVD](QVD)
group by "Portal Order No."
;

[davyqliks]

You absolutely nailed it

Thank you so much.

Kind regards,

Daniel