Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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),
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;
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),
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!
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