concatenate column names with condition

[Pico]

Hi all,

I have a table like this in my DataBase:

Name	World	Excel	PowerPoint	Note
Nik	1	0	1	1
mark	1	0	0	1
Elise	0	1	0	0

I am loading it into Sense, and I would like to end up with a table like the folowing one, possibly using only scripts commands:

Name	World	Excel	PowerPoint	Note	Total
Nik	1	0	1	1	World, PowerPoint, Note
mark	1	0	0	1	World, Note
Elise	0	1	0	0	Excel

So I would like to use the boolean in the cells to add (append) the name of the relative column to a string, specifically to another cell in the same table.

Is it possible to achieve so? How?

Thank you very much.

Nicolo

[RafaelBarrios · Accepted solution]

Hi @Pico 

you should go for crosstables

https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...

#1 full table load

#2 Crosstable with resident

#3 load the second tables using Group by and concat(field,',') to generate that last column
and Join with the first table by name

Hope this works for you.

Best

[QFabian]

Hi @Pico , please check this :

Data:
Load * INLINE [
Name, World, Excel, PowerPoint, Note
Nik, 1, 0, 1, 1
mark, 1, 0, 0, 1
Elise, 0, 1, 0, 0
];

left join
Load
Name,
replace(trim(if(World = 1, 'World ') & if(Excel = 1, 'Excel ') & if(PowerPoint = 1, 'PowerPoint ') & if(Note = 1, 'Note ')), ' ', ', ') as Total
Resident Data;