topic Convert concatenated numbers to num in QlikView

Convert concatenated numbers to num

zhadrakas — Tue, 13 Sep 2016 11:43:39 GMT

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

Re: Convert concatenated numbers to num

Anil_Babu_Samineni — Tue, 13 Sep 2016 11:47:41 GMT

Try this

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

Re: Convert concatenated numbers to num

t_moutault — Tue, 13 Sep 2016 11:50:16 GMT

hi,

have you try to add the format :

num(

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

, '0000000000000000')

Re: Convert concatenated numbers to num

zhadrakas — Tue, 13 Sep 2016 11:57:36 GMT

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

sunny_talwar — Tue, 13 Sep 2016 12:28:17 GMT

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

t_moutault — Tue, 13 Sep 2016 12:29:05 GMT

like this

num(

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

, '0000000000000000')

Re: Convert concatenated numbers to num

zhadrakas — Tue, 13 Sep 2016 12:36:47 GMT

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

Re: Convert concatenated numbers to num

sunny_talwar — Tue, 13 Sep 2016 12:38:55 GMT

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

sunny_talwar — Tue, 13 Sep 2016 12:40:00 GMT

Look here:

Loading numbers in QlikView with more than 14 digits leads into scientific (exponential) notation. The 64-bit IEEE float…

Re: Convert concatenated numbers to num

marcus_sommer — Tue, 13 Sep 2016 13:27:22 GMT

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

MarcoWedel — Tue, 13 Sep 2016 20:52:54 GMT

Hi,

one solution might be:

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

table1:
LOAD *,
    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;
LOAD Ceil(Pow(10,Rand()*4)) as Store_ID,
    Ceil(Pow(10,Rand()*6)) as BusinessDay,
    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

swuehl — Tue, 13 Sep 2016 21:20:42 GMT

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]

Re: Convert concatenated numbers to num

zhadrakas — Wed, 14 Sep 2016 05:50:12 GMT

thanks for your answers.

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)

num(BusinessDay)*1000000+Cashier*10000+Trans_ID

Re: Convert concatenated numbers to num

swuehl — Wed, 14 Sep 2016 05:57:50 GMT

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

zhadrakas — Wed, 14 Sep 2016 06:06:14 GMT

1) yes its a key field

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

Re: Convert concatenated numbers to num

swuehl — Wed, 14 Sep 2016 06:09:43 GMT

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

marcus_sommer — Wed, 14 Sep 2016 06:24:04 GMT

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

- Marcus

Re: Convert concatenated numbers to num

sonysree88 — Wed, 19 Oct 2016 17:03:55 GMT

Hi,

We have to write like this to concatenate

num(

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

, '0000000000000000')

Regards

Sonysree88