Same if condition for more than 10 columns

[mfarsln]

Hi,

I have a table which has columns GRP1, GRP2, GRP3, GRP4 .... GRP15. All of these columns have values 0, 1 or 2. What i want to do is creating new columns for each of these columns and assign values depending on the main column. 

This is what i want to do for all of these columns.

if(GRP1=1 or GRP1=2, 1, 0) as s_GRP1

Is there any easy way? Actually i don't want to write 15 if sentences 🙂

Thanks in advance.

[vunguyenq89 · Accepted solution]

Hi,

You can leverage dollar sign expansion in Qlik to generate the If statements for you in a loop. Below is a sample script with some random data

// Number of GRP* column in the table
Set vColNum = 15; 

// Generate if statements to derive columns s_GRP1 to s_GRP[n]
vCreateDerivedColumnScript = '';
For i=1 to $(vColNum)
	vCreateDerivedColumnScript = vCreateDerivedColumnScript & 'if(GRP$(i)=1 or GRP$(i)=2, 1, 0) as s_GRP$(i)';
    If i < $(vColNum) then 
    	vCreateDerivedColumnScript = vCreateDerivedColumnScript & ',';
    End if;
Next i;

// Create data table
Data:
LOAD 
	*,
    $(vCreateDerivedColumnScript);
LOAD 
RecNo() as ID,
Floor(Rand()*3) as GRP1,
Floor(Rand()*3) as GRP2,
Floor(Rand()*3) as GRP3,
Floor(Rand()*3) as GRP4,
Floor(Rand()*3) as GRP5,
Floor(Rand()*3) as GRP6,
Floor(Rand()*3) as GRP7,
Floor(Rand()*3) as GRP8,
Floor(Rand()*3) as GRP9,
Floor(Rand()*3) as GRP10,
Floor(Rand()*3) as GRP11,
Floor(Rand()*3) as GRP12,
Floor(Rand()*3) as GRP13,
Floor(Rand()*3) as GRP14,
Floor(Rand()*3) as GRP15
AutoGenerate 10;

Result of the script 

Hope it helps.

BR,

Vu Nguyen