Solved: Convert concatenated numbers to num - Qlik Community

zhadrakas · ‎2016-09-13

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

swuehl · ‎2016-09-13

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]

View solution in original post

Anil_Babu_Samineni · ‎2016-09-13

Try this

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

t_moutault · ‎2016-09-13

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 · ‎2016-09-13

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: '-'

sunny_talwar · ‎2016-09-13

May be this:

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

t_moutault · ‎2016-09-13

like this

num(

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

, '0000000000000000')

zhadrakas · ‎2016-09-13

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

sunny_talwar · ‎2016-09-13

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

sunny_talwar · ‎2016-09-13

Look here:

Loading numbers in QlikView with more than 14 digits leads into scientific (exponential) notation. T...

marcus_sommer · ‎2016-09-13

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