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