Loading number and text formats

[#1]

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!

[sunny_talwar]

How about bringing them all as text:

Text(_ItemID)

[#3]

purgchar(_ItemID,'0123456789')

[#4]

Text(_ItemID) just moves everything left

[#5]

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

My issue is with the ones like 1543.000

[sunny_talwar · Accepted solution]

How about this:

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

[#7]

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

[#8]

This worked. Thank you.

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

[sunny_talwar]

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

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

[#10]

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