topic Re: Syntax error in a LOAD - cannot spot it - help plz in QlikView

Syntax error in a LOAD - cannot spot it - help plz

datanibbler — Fri, 13 Nov 2015 08:25:00 GMT

Hi,

I have a LOAD that is a bit complicated because every month, an additional column is inserted into the list at a specific position.

For that reason, I first have a FIRST1 LOAD to have all the fieldnames and then the fields from 33 to 45 in my example (12 months) are checked and if their name has a certain format, variables are filled with that name and an alias - otherwise, both the field_variable and the alias_variable are 'dummy_37' (for line 37 for instance).

After all the variables have been filled, there is the actual LOAD with all the fields that I know plus those variables.

I will paste it here. I have built those lines in Excel as I didn't want to type 12 lines of that sort ... for some reason, the lines after the first one using those variables are not recognized, the commands are not highlighted in blue. I have checked them all several times and I cannot spot the error - particularly because the first one seems fine and the next ones are exactly the same ...

Staplergrundkosten:
LOAD
...,

...,

IF(LEFT('$(v_field_33)', 5) <> 'dummy', '$(v_field_33)' as $(AS_v_field_33)),
IF(LEFT('$(v_field_34)', 5) <> 'dummy', '$(v_field_34)' as $(AS_v_field_34)),
IF(LEFT('$(v_field_35)', 5) <> 'dummy', '$(v_field_35)' as $(AS_v_field_35)),
IF(LEFT('$(v_field_36)', 5) <> 'dummy', '$(v_field_36)' as $(AS_v_field_36)),
IF(LEFT('$(v_field_37)', 5) <> 'dummy', '$(v_field_37)' as $(AS_v_field_37)),
IF(LEFT('$(v_field_38)', 5) <> 'dummy', '$(v_field_38)' as $(AS_v_field_38)),
IF(LEFT('$(v_field_39)', 5) <> 'dummy', '$(v_field_39)' as $(AS_v_field_39)),
IF(LEFT('$(v_field_40)', 5) <> 'dummy', '$(v_field_40)' as $(AS_v_field_40)),
IF(LEFT('$(v_field_41)', 5) <> 'dummy', '$(v_field_41)' as $(AS_v_field_41)),
IF(LEFT('$(v_field_42)', 5) <> 'dummy', '$(v_field_42)' as $(AS_v_field_42)),
IF(LEFT('$(v_field_43)', 5) <> 'dummy', '$(v_field_43)' as $(AS_v_field_43)),
IF(LEFT('$(v_field_44)', 5) <> 'dummy', '$(v_field_44)' as $(AS_v_field_44)),
IF(LEFT('$(v_field_45)', 5) <> 'dummy', '$(v_field_45)' as $(AS_v_field_45)),

...

FROM

...

Can anybody give me a hint as to why the first line seems fine, but all the following ones - which should be the exact same as I did them all in Excel using a formula - are not recognized?

Thanks a lot!

Best regards,

DataNibbler

Re: Syntax error in a LOAD - cannot spot it - help plz

maxgro — Fri, 13 Nov 2015 08:29:27 GMT

the commented one is wrong (syntax), replace with the bold one

LOAD

//IF(LEFT('$(v_field_33)', 5) <> 'dummy', '$(v_field_33)' as $(AS_v_field_33)),

IF(LEFT('$(v_field_33)', 5) <> 'dummy', '$(v_field_33)') as $(AS_v_field_33),

IF(LEFT('$(v_field_34)', 5) <> 'dummy', '$(v_field_34)') as $(AS_v_field_34)

AutoGenerate 2;

EDIT:

this is my test script

let v_field_33 = 'abcdefghi';

let AS_v_field_33 = 'field33';

let v_field_34 = 'abcdefghi';

let AS_v_field_34 = 'field34';

LOAD

//IF(LEFT('$(v_field_33)', 5) <> 'dummy', '$(v_field_33)' as $(AS_v_field_33)),

IF(LEFT('$(v_field_33)', 5) <> 'dummy', '$(v_field_33)') as $(AS_v_field_33),

IF(LEFT('$(v_field_34)', 5) <> 'dummy', '$(v_field_34)') as $(AS_v_field_34)

AutoGenerate 2;

Re: Syntax error in a LOAD - cannot spot it - help plz

Anonymous — Fri, 13 Nov 2015 08:30:10 GMT

Try like this:

You need to close bracket before as

IF(LEFT('$(v_field_33)', 5) <> 'dummy', '$(v_field_33)') as $(AS_v_field_33),

IF(LEFT('$(v_field_34)', 5) <> 'dummy', '$(v_field_34)') as $(AS_v_field_34),

Re: Syntax error in a LOAD - cannot spot it - help plz

datanibbler — Fri, 13 Nov 2015 08:33:06 GMT

Hi,

@ maxgro,

Sorry, I cannot spot the difference between what I have and what you propose. Would you mind explaining?

@ Balraj

Ah, ok, now I see. I'll try.

Thanks a lot!

Re: Syntax error in a LOAD - cannot spot it - help plz

maxgro — Fri, 13 Nov 2015 08:36:55 GMT

the bracket are different

IF(LEFT('$(v_field_33)', 5) <> 'dummy', '$(v_field_33)' as $(AS_v_field_33)), // you

IF(LEFT('$(v_field_33)', 5) <> 'dummy', '$(v_field_33)') as $(AS_v_field_33), // maxgro