2 Replies Latest reply: Jan 10, 2017 9:29 AM by Zayd Derweesh RSS

    Some problem with Replace in LOAD

    Zayd Derweesh

      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!

        • Re: Some problem with Replace in LOAD
          Sunny Talwar

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