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

(ooxml, embedded labels, header is 1 lines, table is [Catering Groups])

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

.

[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

(ooxml, embedded labels, header is 1 lines, table is [Catering Groups]);

Regards,

Nilesh Gangurde

[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

(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?

.

[nilesh_gangurde]

Dont use while in your script,

No need of while..

just copy the script from my last post...

Nilesh Gangurde

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

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

.

[#7]

How about this?

I changed column names..

Example1:

LOAD Fromf,

To,

PurgeChar(From1,'-') as From2,

PurgeChar(To1,'-') as To2,

Fromf + IterNo()- 1 as CC

FROM Sample.xls (biff, embedded labels, table is Sheet2$)

While (Fromf + IterNo()-1) <= To;

This got rid of the - in the two fields..