Referencing a renamed column within a load

[Anonymous]

I am trying to reference a column name that has If Else statements modifying the column but I keep getting and error when loading the data saying filed not found. For example:

[table_name]:

LOAD

     AOD,

     FY,

     IF("field_name" = 'October', 'OCT',

          IF("field_name" = 'November', 'NOV',

               IF("field_name" = 'December', 'DEC',

                    IF("field_name" = 'January', 'JAN',

                         IF("field_name" = 'February', 'FEB',

                              IF("field_name" = 'March', 'MAR',

                                   IF("field_name" = 'April', 'APR',

                                        IF("field_name" = 'May', 'MAY',

                                             IF("field_name" = 'June', 'JUN',

                                                  IF("field_name" = 'July', 'JUL',

                                                       IF("field_name" = 'August', 'AUG', 'SEP'))))))))))) AS new_field_name,  

     DATE(AOD) & '|' & FY & '|' & new_field_name AS joinkey

FROM  [lib://AttachedFiles/Excel_doc.xls]

(biff, embedded labels, table is [Sheet 1$]);

This returns an arror "Field 'new_field_name' not found.

[Nicole-Smith]

It's because you're referencing the field before it is actually created:

[table_name]:

LOAD

    AOD,

    FY,

    IF("field_name" = 'October', 'OCT',

          IF("field_name" = 'November', 'NOV',

              IF("field_name" = 'December', 'DEC',

                    IF("field_name" = 'January', 'JAN',

                        IF("field_name" = 'February', 'FEB',

                              IF("field_name" = 'March', 'MAR',

                                  IF("field_name" = 'April', 'APR',

                                        IF("field_name" = 'May', 'MAY',

                                            IF("field_name" = 'June', 'JUN',

                                                  IF("field_name" = 'July', 'JUL',

                                                      IF("field_name" = 'August', 'AUG', 'SEP'))))))))))) AS new_field_name, 

    DATE(AOD) & '|' & FY & '|' & new_field_name AS joinkey

FROM  [lib://AttachedFiles/Excel_doc.xls]

(biff, embedded labels, table is [Sheet 1$]);

Updating it to use a preceding load should fix the issue:

[table_name]:

LOAD *,

     DATE(AOD) & '|' & FY & '|' & new_field_name AS joinkey;

LOAD

     AOD,

     FY,

     IF("field_name" = 'October', 'OCT',

          IF("field_name" = 'November', 'NOV',

               IF("field_name" = 'December', 'DEC',

                    IF("field_name" = 'January', 'JAN',

                         IF("field_name" = 'February', 'FEB',

                              IF("field_name" = 'March', 'MAR',

                                   IF("field_name" = 'April', 'APR',

                                        IF("field_name" = 'May', 'MAY',

                                             IF("field_name" = 'June', 'JUN',

                                                  IF("field_name" = 'July', 'JUL',

                                                       IF("field_name" = 'August', 'AUG', 'SEP'))))))))))) AS new_field_name

FROM  [lib://AttachedFiles/Excel_doc.xls]

(biff, embedded labels, table is [Sheet 1$]);

[vishsaggi]

You cannot dynamically reference the fieldname in the Load that is creating. Using Preceding load you can try like:

[table_name]:

LOAD *,

     DATE(AOD) & '|' & FY & '|' & new_field_name AS joinkey;

LOAD AOD,

     FY,

     IF("field_name" = 'October', 'OCT',

          IF("field_name" = 'November', 'NOV',

               IF("field_name" = 'December', 'DEC',

                    IF("field_name" = 'January', 'JAN',

                         IF("field_name" = 'February', 'FEB',

                              IF("field_name" = 'March', 'MAR',

                                   IF("field_name" = 'April', 'APR',

                                        IF("field_name" = 'May', 'MAY',

                                             IF("field_name" = 'June', 'JUN',

                                                  IF("field_name" = 'July', 'JUL',

                                                       IF("field_name" = 'August', 'AUG', 'SEP'))))))))))) AS new_field_name 

FROM  [lib://AttachedFiles/Excel_doc.xls]

(biff, embedded labels, table is [Sheet 1$]);

[Nicole-Smith]

Also, you can shorten up that if statement a lot by doing this:

[table_name]:

LOAD *,

     DATE(AOD) & '|' & FY & '|' & new_field_name AS joinkey;

LOAD

     AOD,

     FY,

     UPPER(LEFT("field_name", 3)) AS new_field_name

FROM  [lib://AttachedFiles/Excel_doc.xls]

(biff, embedded labels, table is [Sheet 1$]);