Creating a new field

[#1]

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

[swuehl]

Try

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

to format and check for NULL.

[#3]

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

[swuehl]

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

];

[rustyfishbones]

What a great solution swuehl

[#6]

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"

[#7]

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

[shree909]

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

[#9 · Accepted solution]

Hi Glenn,

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

[swuehl]

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?