topic Re: How to conditionally fetch column name as row value ? in QlikView

How to conditionally fetch column name as row value ?

shekhar_analyti — Tue, 23 Mar 2021 09:09:54 GMT

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 .

Re: How to conditionally fetch column name as row value ?

QFabian — Tue, 23 Mar 2021 13:02:12 GMT

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;

Re: How to conditionally fetch column name as row value ?

eddie_wagt — Tue, 23 Mar 2021 13:40:35 GMT

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 ;

Re: How to conditionally fetch column name as row value ?

shekhar_analyti — Wed, 24 Mar 2021 05:49:34 GMT

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

Re: How to conditionally fetch column name as row value ?

shekhar_analyti — Wed, 24 Mar 2021 05:52:24 GMT

Thank You QFabian for the reply .

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