Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
zhadrakas
Valued Contributor

Convert concatenated numbers to num

Hello,

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

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

QlikView doesnt Interpret this as a number.

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

Thanks in advance for help

Tim

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

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;

AutonumberHash256( Store_ID,BusinessDay,cashier,Trans_ID) as Key

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

17 Replies

Re: Convert concatenated numbers to num

Try this


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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
t_moutault
New Contributor III

Re: Convert concatenated numbers to num

hi,

have you try to add the format :

num(

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

, '0000000000000000')

zhadrakas
Valued Contributor

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

May be this:

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

t_moutault
New Contributor III

Re: Convert concatenated numbers to num

like this

num(

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

, '0000000000000000')

zhadrakas
Valued Contributor

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

MVP & Luminary
MVP & Luminary

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