How to conditionally fetch column name as row value ?

[shekhar_analyti]

Hi All ,

How to conditionally fetch column name as row value at script level , without using multiple nested if  ?

Source data 

ID	Location	M1	M2	M3	M4	M5
P1	R1	0.4	0.7
P2	R1	0.1				0.8
P3	R1				0.6	0.3
P4	R1				0.6	0.8
P1	R2			0.1	0.3
P2	R2			0.7	0.0
P3	R2	0.5				0.4
P4	R2	0.3				0.3
P1	R3		0.1	0.0
P2	R3		0.2		0.3
P3	R3		0.7			0.3
P4	R3	0.9			0.5

OUTPUT 

ID	Location	M1	M2	M3	M4	M5	COLUMN1
P1	R1	0.4	0.7				M1,M2
P2	R1	0.1				0.8	M1,M5
P3	R1				0.6	0.3	M4,M5
P4	R1				0.6	0.8	M4,M5
P1	R2			0.1	0.3		M3,M4
P2	R2			0.7	0.0		M3,M4
P3	R2	0.5				0.4	M1,M5
P4	R2	0.3				0.3	M1,M5
P1	R3		0.1	0.0			M2,M3
P2	R3		0.2		0.3		M2,M4
P3	R3		0.7			0.3	M2,M5
P4	R3	0.9			0.5		M1,M4

Thank You .

[QFabian]

Hi @shekhar_analyti , here an example, it has if, but they are not nested.

Load
ID, Location,

if(not isnull(M1) or M1 <>'', 'M1,') &
if(not isnull(M2), 'M2,') &
if(not isnull(M3), 'M3,') &
if(not isnull(M4), 'M4,') &
if(not isnull(M5), 'M5,') as M
Resident Data;

[eddie_wagt · Accepted solution]

Hello @shekhar_analyti,

If you want to do this dynamically, you can opt for some flexibility in the script.

Source:
LOAD * INLINE[
ID	Location	M1	M2	M3	M4	M5
P1	R1	0.4	0.7	 	 	 
P2	R1	0.1	 	 	 	0.8
P3	R1	 	 	 	0.6	0.3
P4	R1	 	 	 	0.6	0.8
P1	R2	 	 	0.1	0.3	 
P2	R2	 	 	0.7	0.0	 
P3	R2	0.5	 	 	 	0.4
P4	R2	0.3	 	 	 	0.3
P1	R3	 	0.1	0.0	 	 
P2	R3	 	0.2	 	0.3	 
P3	R3	 	0.7	 	 	0.3
P4	R3	0.9	 	 	0.5	 
]  (delimiter is '	');

LET NoFields = NoOfFields('Source');

For i=3 to $(NoFields)

LET FieldName = FieldName($(i),'Source');

TMP:
LOAD 	ID
,		Location
,		'$(FieldName)' as TMP_COLUMN1
Resident Source
Where $(FieldName)>0;

next;

left join (Source)
LOAD 	ID
,		Location
,		concat(TMP_COLUMN1,',') as COLUMN1
Resident TMP
Group By 	ID
,			Location
;

Drop Table TMP
;

[shekhar_analyti]

Thank You eddie , hope this works fine with huge data . 

[shekhar_analyti]

Thank You QFabian for the reply . 

But i was looking to avoid multiple if and hardcoding column names .