Convert concatenated numbers to num

[zhadrakas]

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

[MarcoWedel]

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

[swuehl · Accepted solution]

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]

[zhadrakas]

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

[swuehl]

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?

[zhadrakas]

1) yes its a key field

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

[swuehl]

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.

[marcus_sommer]

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

- Marcus

[sonysree88]

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