6 Replies Latest reply: May 16, 2012 7:28 PM by smhshaqv RSS

    how to remove - from data in a load script

    hopkinsc
      Hi, i am loading in some data for Cost Centres, the data are all numbers but they have a - after each number..
      eg..
      101-
      102-
      103-
      etc. I am trying to use a WHILE ITERNO () function in the script but it doesnt like the format of the numbers.
      Is there a way i can remove these before using the WHILE function?
      I have attached a sample of the data and here is my script...
      [Expense Codes]:
      LOAD From,
      To,
      From1,
      To1
      F AS Desc,
      From1 + IterNo()- 1 as CC
      FROM
      [C:\Users\hopkinscn\Desktop\Catering PL Mapping.xlsx]
      (
      ooxml, embedded labels, header is 1 lines, table is [Catering Groups])

      While (From1 + IterNo()-1) <= To1;
      .
        • how to remove - from data in a load script
          Nilesh Gangurde

          Hi Hopkin,

           

          Use Subfield Function

           

          Subfield(Form1,'-',1) as NewForm1,

          subfield(To1,'-',1) as NewTo1,

           

          then your script will be :

          LOAD From,
          To,
          From1,
          To1

          Subfield(Form1,'-',1) as NewForm1,
          subfield(To1,'-',1) as NewTo1,
          FROM
          [C:\Users\hopkinscn\Desktop\Catering PL Mapping.xlsx]
          (
          ooxml, embedded labels, header is 1 lines, table is [Catering Groups]);

           

           

          Regards,

          Nilesh Gangurde

            • how to remove - from data in a load script
              hopkinsc

              Hi Nilesh,

              this is now my script..

              [Expense Codes]:

              LOAD From,
              To,
              From1,
              To1
              F AS Desc,
              Subfield(From1,'-',1) as NewFrom1,
              Subfield(To1,'-',1) as NewTo1,
              From1 + IterNo()- 1 as EC

              FROM
              [C:\Users\hopkinscn\Desktop\Catering PL Mapping.xlsx]
              (
              ooxml, embedded labels, header is 1 lines, table is [Catering Groups])

              While (From1 + IterNo()-1) <= To1;

              but it still doesn't work...

               

              have i done the script correctly?

              .

                • how to remove - from data in a load script
                  Nilesh Gangurde

                  Dont use while in your script,

                  No need of while..

                   

                  just copy the script from my last post...

                   

                   

                  Nilesh Gangurde

                    • Re: how to remove - from data in a load script
                      hopkinsc

                      but the while is to do a loop through the data of the 2 fields... i need to keep that otherwise i cant do what i need to do...

                      • Re: how to remove - from data in a load script
                        hopkinsc
                        let me explain exactly what i am trying to do...
                        if you look at the original sample data, there are 2 categories
                        Cost Code and Expense Code.
                        Each category has has a FROM and TO
                        I am trying to make the FROM and TO a single column containing all possible values. So i am making 4 columns to just 2 columns.
                        Expense Code
                        From         To
                        850-          860-
                        so i want a new table called EC containing:
                        850
                        851
                        852
                        853
                        all the wat to 860
                        But this won't work because each value has - after it.
                        The Cost Code works great as the data is just numeric.
                        any ideas..
                        This is my full script
                        Example1:
                        LOAD From,
                        To,
                        From1,
                        To1,
                        Value,
                        Desc,
                        From + IterNo()- 1 as CC
                        FROM
                        Example.xlsx
                        (
                        ooxml, embedded labels, table is Sheet1)
                        While (From + IterNo()-1) <= To; // Loop through Cost Centers for record before going to next record


                        // Reload file and add records for Expense Codes
                        Example:
                        Load *,
                        From1 + IterNo()- 1 as EC
                        Resident Example1
                        While (From1 + IterNo()-1) <= To1; // Loop through expense codes for record before going to next record

                        // Get rid of original table all data is in new table
                        DROP Table Example1;
                        .