Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Referencing a renamed column within a load

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.


3 Replies
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
Champion III
Champion III

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$]);