topic Re: Creating a new field in QlikView

Creating a new field

Fri, 03 Jan 2014 16:17:15 GMT

Hello I'm new to Qlikview. I want to create a new field in the extract file and I want to make the field a uniform text field in a set number of characters.

For example the original file has the following fields:

Area Depot Shift

1 28 2

3 1

2 125 1

2 3 2

I want the new fields to look like this:

LocationID

01-028-02

03-000-01

02-125-01

02-003-02

In Excel the formulae would be

=TEXT(A2,"00")&"-"&TEXT(B2,"000")&"-"&TEXT(C2,"00")

In Qlikview I can get the new field to look like this:

1-28-2

3-1

2-125-1

2-3-2

by using the formulae

Area&'-'&Depot&'-'&Shift

But I cant get it to add the leading zeros or zeros for the blank field

Re: Creating a new field

swuehl — Fri, 03 Jan 2014 16:22:40 GMT

Try

num(alt(Area,0),'00')&'-'&num(alt(Depot,0),'000')&'-'&num(alt(Shift,0),'00')

to format and check for NULL.

Re: Creating a new field

Fri, 03 Jan 2014 16:34:02 GMT

Needs a tweek that just returns 00-000-000 for all values, unless I've keyed something in wrong

Re: Creating a new field

swuehl — Fri, 03 Jan 2014 16:39:18 GMT

This seems to work:

LOAD

text(num(alt(num#(Area),0),'00')&'-'&num(alt(num#(Depot),0),'000')&'-'&num(alt(num#(Shift),0),'00')) as Code

INLINE [

Area, Depot, Shift

1, 28, 2

3, , 1

2, 125, 1

2, 3 , 2

];

Re: Creating a new field

rustyfishbones — Fri, 03 Jan 2014 16:41:53 GMT

What a great solution swuehl

Re: Creating a new field

Fri, 03 Jan 2014 16:43:24 GMT

I tried:

num(Area,'00')&'-'&num(Depot,'000')&'-'&num(Shift,'00')

This works where there is data but leaves the field blank if there isnt any and puts "--" instead of the required "000"

Re: Creating a new field

Fri, 03 Jan 2014 16:46:31 GMT

Would I have to paste in the whole thing into the formulae

text(num(alt(num#(Area),0),'00')&'-'&num(alt(num#(Depot),0),'000')&'-'&num(alt(num#(Shift),0),'00')) as Code

INLINE [

Area, Depot, Shift

1, 28, 2

3, , 1

2, 125, 1

2, 3 , 2

];

There are thousands of variables in the data & I dont know what they are until Ive imported them

Re: Creating a new field

shree909 — Fri, 03 Jan 2014 16:54:04 GMT

Hi, The other way to write the expression is using the IF Condition..

Try this in the script

if(len(Area)<2,0&Area)&'-'&if(len(Depot)<1,000&Depot ,if( len(Depot)<2,00&Depot,if( len(Depot)<3,0&Depot,Depot)))&'-'&if(len(Shift)<2,0&Shift) AS NewCode

Re: Creating a new field

Fri, 03 Jan 2014 17:21:53 GMT

Hi Glenn,

LOAD text(Alt(Area,'00')&'-'&Alt(Depot,'00')&'-'& Alt(Shift,'00')) as Value Resident Dev01;

Re: Re: Creating a new field

swuehl — Fri, 03 Jan 2014 18:20:41 GMT

The INLINE LOAD was just used for demonstration, use your data source instead.

text(num(alt(num#(Area),0),'00')&'-'&num(alt(num#(Depot),0),'000')&'-'&num(alt(num#(Shift),0),'00')) as Code

The essential part for the format is indeed,

num(Area,'00')&'-'&num(Depot,'000')&'-'&num(Shift,'00')

and to handle blank field values or NULL, I inserted alt(num#(FIELD),0) parts, but you only need to use the alt() thing when you expect missing or NULL values

(Please check the Help for alt() and num#() function). The additional text() function was used to prevent QV from interpreting a value as Date).

There are thousands of variables in the data & I dont know what they are until Ive imported them

What do you mean with that? You don't know the field names you load? What is your data source?