Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
robin_heijt
Contributor

Nested if statement

Hi,

I am trying to create an if statement in my data load editor to combine different values under 1 dimesnion.

As example (It is wrongly written): ,

LOAD * ,IF([Band]="IA" or [Band]="I-A" or [Band]="IB" or [Band]="I-B"),"I",

IF([Band]="IIA" or [Band]="II-A" or [Band]="IIB" or [Band]="II-B"),"II",

IF([Band]="IIIA" or [Band]="III-A" or [Band]="IIIB" or [Band]="III-B"),"III",

IF([Band]="IVA" or [Band]="IV-A" or [Band]="IVB" or [Band]="IV-B"),"IV",

IF([Band]="VA" or [Band]="V-A" or [Band]="VB" or [Band]="V-B"),"V",

IF([Band]="VIA" or [Band]="VI-A" or [Band]="VIB" or [Band]="VI-B"),"VI",

IF([Band]="VIIA" or [Band]="VII-A" or [Band]="VIIB" or [Band]="VII-B"),"VII", Else "ALL"))))))) as Band Unified;

I have 4 different values per unified band. I want them to become unified and every value that is not categorized in this list to be changed into ALL.

Anyone that can help me out correct this code?

1 Solution

Accepted Solutions
ogautier62
Valued Contributor II

Re: Nested if statement

with the first step it should be this :

load *, if(match(purgechar(band,'-AB'),'I','II', ......, 'VII')>0, purgechar(band,'-AB'), 'ALL') as band_unified

View solution in original post

6 Replies
ogautier62
Valued Contributor II

Re: Nested if statement

Hi,

easiest is to use function purgechar() :

load *, purgechar(band,'-AB') as band_unified

it will be left : I, II,III, IV .......

regards

robin_heijt
Contributor

Re: Nested if statement

Thank you,

That actually works too, now I am only left with the issue that I need all bands that are not I-VII to be named "ALL"

These are randomly created band names, and therefore too many to manually assign to ALL.

How could I make Band I-VII show up like it is now in unified, and all other bands to show up as ALL?

Thank you!

ogautier62
Valued Contributor II

Re: Nested if statement

ok, second step you can use match(    :

if(match(band,'I','II', ......, 'VII')>0, band, 'ALL') as ...

robin_heijt
Contributor

Re: Nested if statement

Unfortunately I am getting an error in the last bit of the string : ", band 'ALL') as [Band Unified];"

as error it says that the engine expects "THEN" instead of "," after the 0.

Can you please advice on this?

ogautier62
Valued Contributor II

Re: Nested if statement

with the first step it should be this :

load *, if(match(purgechar(band,'-AB'),'I','II', ......, 'VII')>0, purgechar(band,'-AB'), 'ALL') as band_unified

View solution in original post

robin_heijt
Contributor

Re: Nested if statement

Thank you very much, that did the trick!