Nested if statement

[robin_heijt]

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?

[ogautier62]

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]

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]

ok, second step you can use match(    :

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

[robin_heijt]

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 · Accepted solution]

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

[robin_heijt]

Thank you very much, that did the trick!