Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 .
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
;
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;
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
;
Thank You eddie , hope this works fine with huge data .
Thank You QFabian for the reply .
But i was looking to avoid multiple if and hardcoding column names .