Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

how to remove - from data in a load script

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;
.
6 Replies
nilesh_gangurde
Partner - Specialist
Partner - Specialist

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
Partner - Specialist III
Partner - Specialist III
Author

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
Partner - Specialist
Partner - Specialist

Dont use while in your script,

No need of while..

just copy the script from my last post...

Nilesh Gangurde

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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
Partner - Specialist III
Partner - Specialist III
Author

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

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