topic Re: Loading number and text formats in QlikView

Loading number and text formats

Wed, 03 Aug 2016 15:08:35 GMT

Hi,

I have an issue when loading items into QV.

Some items load as text and some load as numbers. Some have '-' and some have letters in the string. View the image below.

My issue with the item numbers that load as numbers. Instead of loading as item # 15430, it loads as item # 15430.0000.

I have tried using TRIM but that doesn't work. I tried NUM but then it drops all of the item #'s formatted with a '-' or letter in them. I tried TEXT but that just aligns everything on the left.

I need to do this in the load script rather than on the object for example.

Any help would be great!

Re: Loading number and text formats

sunny_talwar — Wed, 03 Aug 2016 15:19:45 GMT

How about bringing them all as text:

Text(_ItemID)

Re: Loading number and text formats

Wed, 03 Aug 2016 15:20:16 GMT

purgchar(_ItemID,'0123456789')

Re: Loading number and text formats

Wed, 03 Aug 2016 15:23:46 GMT

Text(_ItemID) just moves everything left

Re: Loading number and text formats

Wed, 03 Aug 2016 15:24:27 GMT

I need to keep the ones formatted as 123-521 and 134G for example.

My issue is with the ones like 1543.000

Re: Loading number and text formats

sunny_talwar — Wed, 03 Aug 2016 16:07:37 GMT

How about this:

If(IsNum(_ItemID), Num(_ItemID, '##'), _ItemID)

Re: Loading number and text formats

Wed, 03 Aug 2016 16:58:54 GMT

=num( KeepChar('153000','0123456789'),'#.##0')

Re: Loading number and text formats

Wed, 03 Aug 2016 20:00:08 GMT

This worked. Thank you.

When I add my keys to it then it does not work unfortunately. Any idea?

Re: Loading number and text formats

sunny_talwar — Wed, 03 Aug 2016 20:09:53 GMT

Not sure if ItemID is already fixed, but may be this:

$(ConcatKey('$(varCompanyID)', If(IsNum(ItemID), Num(ItemID, '##'), ItemID))) as _ItemID

Re: Loading number and text formats

Wed, 03 Aug 2016 20:48:28 GMT

There is an error message which I believe is because the amount of expressions in the variable

Re: Loading number and text formats

sunny_talwar — Wed, 03 Aug 2016 21:56:41 GMT

From the looks of things, it seems that the second half of the expression isn't taken into the variable you have created. Would you be able to share the definition of vConcatKey variable? You might need to add $2 in addition to $1

Re: Loading number and text formats

Mon, 08 Aug 2016 13:18:39 GMT

Sunny,

ConcatKey = if(trim($1)='' or IsNull($1), '*NONE', trim($1)) & '%' & if(trim($2)='' or IsNull($2), '*NONE', trim($2));