topic how to remove - from data in a load script in QlikView

how to remove - from data in a load script

hopkinsc — Wed, 16 May 2012 19:58:18 GMT

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;

how to remove - from data in a load script

nilesh_gangurde — Wed, 16 May 2012 20:07:02 GMT

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

how to remove - from data in a load script

hopkinsc — Wed, 16 May 2012 20:19:33 GMT

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?

how to remove - from data in a load script

nilesh_gangurde — Wed, 16 May 2012 20:24:57 GMT

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 — Wed, 16 May 2012 20:25:50 GMT

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 — Wed, 16 May 2012 20:37:16 GMT

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;

how to remove - from data in a load script

Wed, 16 May 2012 23:28:57 GMT

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