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;
          .