topic Referencing a renamed column within a load in QlikView

Referencing a renamed column within a load

Anonymous — Thu, 12 Jul 2018 15:01:23 GMT

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.

Re: Referencing a renamed column within a load

Nicole-Smith — Thu, 12 Jul 2018 18:06:15 GMT

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

Re: Referencing a renamed column within a load

vishsaggi — Thu, 12 Jul 2018 18:07:57 GMT

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

Re: Referencing a renamed column within a load

Nicole-Smith — Thu, 12 Jul 2018 18:08:02 GMT

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