Some problem with Replace in LOAD

[#1]

Hello friends,

I am trying to load data into QlikSense, and then create some fields and alter the content of those fields. Specifically, I am trying to create the field AREA_OFFICE_TRIM from AREA_OFFICE, and then replace the string inside AREA_OFFICE_TRIM. It works if I do not try the last part, but when I put the code to change the string it tells me AREA_OFFICE TRIM not found. Code below:

table1:
LOAD
    pkey,
    trans_ID,
    trans_date,
    debit_amt,
    credit_amt,
    area_office,
    ch_id,

// The next set of fields are calculated, and will be included in final_flag_detail table:

    WeekDay (trans_date) as trans_weekday,
    Day(trans_date) as trans_day,
    Month(trans_date) as trans_month,
    Year(trans_date) as trans_year,
    debit_amt + credit_amt as trans_amt,
LTrim(KeepChar(AO_name,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ .,-')) as ao_name_tr, /*removes numeric values in AO name by keeping only characters listed; also trims leading space */
LTrim(KeepChar(area_office,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ .,-')) as area_office_trim,
    Replace(area_office_trim, 'LONDON ', 'UK'), /*if I include this line it stops working */
    Replace(area_office_trim, 'HQ ', 'HEADQUARTERS'), /* can I include this line if I included the previous one also? */
//   Pick(Match(area_office_trim,'LONDON'), 'UK') AS area_office_trim_new, /*is this different from replace? */

   
FROM [lib://table1.xlsx]
(ooxml, embedded labels, table is [TableDetail]);

For example, I want the field AREA_OFFICE TRIM to display UK if it originally contained the string 'LONDON OFFICE' or 'LONDON BUILDING', etc. and to display 'HEADQUARTERS' if it originally displayed 'HQ'. I want to do multiple of these.

Thanks!

[sunny_talwar · Accepted solution]

You cannot use a new field created in the same load statement. You can either use Resident load or preceding load to do this

1) Preceding load way:

table1:

LOAD *,

    Replace(area_office_trim, 'LONDON ', 'UK'), /*if I include this line it stops working */
    Replace(area_office_trim, 'HQ ', 'HEADQUARTERS'), /* can I include this line if I included the previous one also? */
//  Pick(Match(area_office_trim,'LONDON'), 'UK') AS area_office_trim_new; /*is this different from replace? */

LOAD
    pkey,
    trans_ID,
    trans_date,
    debit_amt,
    credit_amt,
    area_office,
    ch_id,

// The next set of fields are calculated, and will be included in final_flag_detail table:

    WeekDay (trans_date) as trans_weekday,
    Day(trans_date) as trans_day,
    Month(trans_date) as trans_month,
    Year(trans_date) as trans_year,
    debit_amt + credit_amt as trans_amt,
LTrim(KeepChar(AO_name,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ .,-')) as ao_name_tr, /*removes numeric values in AO name by keeping only characters listed; also trims leading space */
LTrim(KeepChar(area_office,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ .,-')) as area_office_trim

FROM [lib://table1.xlsx]
(ooxml, embedded labels, table is [TableDetail]);

2) Resident load way:

table1:

LOAD
    pkey,
    trans_ID,
    trans_date,
    debit_amt,
    credit_amt,
    area_office,
    ch_id,

// The next set of fields are calculated, and will be included in final_flag_detail table:

    WeekDay (trans_date) as trans_weekday,
    Day(trans_date) as trans_day,
    Month(trans_date) as trans_month,
    Year(trans_date) as trans_year,
    debit_amt + credit_amt as trans_amt,
LTrim(KeepChar(AO_name,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ .,-')) as ao_name_tr, /*removes numeric values in AO name by keeping only characters listed; also trims leading space */
LTrim(KeepChar(area_office,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ .,-')) as area_office_trim

FROM [lib://table1.xlsx]
(ooxml, embedded labels, table is [TableDetail]);

table2:

LOAD *,

    Replace(area_office_trim, 'LONDON ', 'UK'), /*if I include this line it stops working */
    Replace(area_office_trim, 'HQ ', 'HEADQUARTERS'), /* can I include this line if I included the previous one also? */
//  Pick(Match(area_office_trim,'LONDON'), 'UK') AS area_office_trim_new; /*is this different from replace? */

Resident table1;

DROP Table table1;

3) Same load but use the whole definition

table1:
LOAD
    pkey,
    trans_ID,
    trans_date,
    debit_amt,
    credit_amt,
    area_office,
    ch_id,

// The next set of fields are calculated, and will be included in final_flag_detail table:

    WeekDay (trans_date) as trans_weekday,
    Day(trans_date) as trans_day,
    Month(trans_date) as trans_month,
    Year(trans_date) as trans_year,
    debit_amt + credit_amt as trans_amt,
LTrim(KeepChar(AO_name,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ .,-')) as ao_name_tr, /*removes numeric values in AO name by keeping only characters listed; also trims leading space */
LTrim(KeepChar(area_office,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ .,-')) as area_office_trim,
    Replace(LTrim(KeepChar(area_office,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ .,-')), 'LONDON ', 'UK'), /*if I include this line it stops working */
    Replace(LTrim(KeepChar(area_office,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ .,-')), 'HQ ', 'HEADQUARTERS'), /* can I include this line if I included the previous one also? */
//   Pick(Match(LTrim(KeepChar(area_office,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ .,-')),'LONDON'), 'UK') AS area_office_trim_new, /*is this different from replace? */

FROM [lib://table1.xlsx]
(ooxml, embedded labels, table is [TableDetail]);

[#3]

Thanks Sunny - this is perfect. I am new, so did not know about preceding loads and resident loads. This will be helpful in future as well.

Thanks again!