17 Replies Latest reply: Oct 19, 2016 1:03 PM by sonygot g

# Convert concatenated numbers to num

Hello,

i want to generate a concatenated number out of the following fields.

QlikView doesnt Interpret this as a number.

Is there a trick to get a number out of that?

Tim

• ###### Re: Convert concatenated numbers to num

Try this

• ###### Re: Convert concatenated numbers to num

hi,

have you try to add the format :

num(

, '0000000000000000')

• ###### Re: Convert concatenated numbers to num

doesn't work.

Input: num(Store_ID, '0000')&num(BusinessDay, '000000')&num(cashier, '00')&num(Trans_ID, '0000'))

Output: 155542522041399

Input: num(num(Store_ID, '0000')&num(BusinessDay, '000000')&num(cashier, '00')&num(Trans_ID, '0000')), '00000000000000')

Output: '-'

• ###### Re: Convert concatenated numbers to num

like this

num(

, '0000000000000000')

• ###### Re: Convert concatenated numbers to num

May be this:

Num#(Num(Store_ID, '0000') & Num(BusinessDay, '000000') & Num(cashier, '00') & Num(Trans_ID, '0000'), '0000000000000000')

• ###### Re: Convert concatenated numbers to num

doesn't work either. it Shows the number. but the value is not interpreted as number.

• ###### Re: Convert concatenated numbers to num

I guess the number is greater than 14 digits and QlikView has problem with numbers which are greater than 14 digits

• ###### Re: Convert concatenated numbers to num

If you concatenate numbers with the &-sign you will create a string and not a number. Therefore I think you will need a different approach like:

([StoreID] * pow(10, 4)) + ([OrderID] * pow(10, 9)) + [Pos] as [UniqueKey]

which worked great in my case. If your key isn't necessary between different qvw's or to incremental load from qvd's you could also put your key into an autonumber() function (whereby if you used a string-concatenation of numbers you might need a delimiter like '|' between them to ensure that each key is unique - and no num(x, '0000') is necessary).

- Marcus

• ###### Re: Convert concatenated numbers to num

Hi,

one solution might be:

Evaluate(Num(Store_ID,'0000') & Num(BusinessDay,'000000') & Num(cashier,'00') & Num(Trans_ID,'0000'))

```table1:
Num(Store_ID,'0000') & Num(BusinessDay,'000000') & Num(cashier,'00') & Num(Trans_ID,'0000') as ConcatTxt,
Evaluate(Num(Store_ID,'0000') & Num(BusinessDay,'000000') & Num(cashier,'00') & Num(Trans_ID,'0000')) as ConcatNum;
Ceil(Pow(10,Rand()*2)) as cashier,
Ceil(Pow(10,Rand()*4)) as Trans_ID
AutoGenerate 30;
```

hope this helps

regards

Marco

• ###### Re: Convert concatenated numbers to num

I think Evaluate() will only create a dual value with the concatenated text value and a numeric value that is an approximation to the integer value, as best as the IEEE representation allows.

If your numbers differ only in least significant digits, QV may not be able to distinguish them, so you may lose information.

Maybe you won't see this with a limited data set, but definitely it may become an issue when you add more digits to your field values.

It may be best to either use just the text value (so you don't need to store the numeric representation that may not be helpful anyway) or use an autonumber instead;

[in case you want to create a numeric key field based on the four field values]

• ###### Re: Convert concatenated numbers to num

I really Need that value to be a number.

Autonumber would be a good solution but my customer want to see the real cashier and trans_ID out of the merged number. So i went this way (now i needed to drop the Store_ID but thats ok in my case)

• ###### Re: Convert concatenated numbers to num

You will be able to see the original numbers when using a textual value, too (textual in the way it's stored in QV, it still 'looks' like a number).

You can also create a dual value with the text representation and autonumber as numeric representation, if you absolutely need to.

In which context are you using these long concatenated numbers? What is the purpose to concatenate them to a string? A key field?

• ###### Re: Convert concatenated numbers to num

1) yes its a key field

2) i want to calculate the next x and previous x Transactions

• ###### Re: Convert concatenated numbers to num

You should still be able to do 2) either by keeping the original field values, or, since you know the format specification of your concatenated text value, by manipulating the string.

• ###### Re: Convert concatenated numbers to num

Won't it not be easier beside the key to keep the fields which create them?

- Marcus

• ###### Re: Convert concatenated numbers to num

Hi,

We have to write like this to concatenate

num(