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: 
Not applicable

Data Manipulation on Import

I have a field A with values like this: 00123, 00234, 022, 33, 234M, PSR44

I need the zeroes before the fields to go, so i get a set of values that looks like this: 123, 234, 22, 33, 234M, PSR44

I can do this in excel by multiplying by 1 and using an iferror statement that shows the original statement in case of an error (for values with letters)... How can i do this in QV? (I kno i can multiply the field by 1 while importing, is there an equivalent of iferror that i can use there?)

Thanks!

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

Hi,

Use as follows in the load script:

Alt(Num(FieldA), FieldA) AS FieldA

The Alt() function will return the first numeric value that returns true. Num(000234) returns 234 removing the leading zeroes, so that should work. If the value is not numeric, the it returns the same value.

Hope that helps.

Miguel

View solution in original post

4 Replies
Miguel_Angel_Baeyens

Hi,

Use as follows in the load script:

Alt(Num(FieldA), FieldA) AS FieldA

The Alt() function will return the first numeric value that returns true. Num(000234) returns 234 removing the leading zeroes, so that should work. If the value is not numeric, the it returns the same value.

Hope that helps.

Miguel

Not applicable
Author

And if i ant to rename field A to field X while doing all of the above?

Not applicable
Author

write the field as field A as [field x]

Sampath

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

In the statement given by Miguel you will find the Word FieldA, this is nothing but a new name for field A.

You rename the field with Keyword "As".

Hope this is clear   

Regards,

Kaushik Solanki   

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!