How to make $ 95.8 display as 95.8 ?

[#1]

Hi All

My excel raw data price list is enter as $         95.70 , hence when i load into QV , it display as $        95.8

I want to remove those extra space.

I try below , all fail :-

TRIM('SGD '&[LIST_PRICE]) as LISTPRICE,

round(LIST_PRICE,0.01) as LIST_PRICE,

num(TRIM([LIST_PRICE])) as LIST_PRICE_,

Paul

[ariel_klien]

Hi

Try

KeepChar([LIST_PRICE],'1234567890.') as [LIST_PRICE]

BR

Ariel

[jpapador]

Try PurgeChar(LIST_PRICE, ' ') as [List Price]

[#4]

Hi All

Both recommendation not able to work , any more help will be appreciated.

Paul

[Vegar · Accepted solution]

On my computer this code will make numeric out of your excel.

MITSUBISHI:

LOAD [Item No.],

  'Inverter' as [Product Group],

  'MITSUBISHI' AS BRAND_P,

     [Part No.] as [PART_NO_],

     [Distributor List Price in SGD (S$)],

     [Distributor Discount]  as [LIST_PRICE],

     [Distributor Discount]  as [LIST_PRICE_],

     TRIM([Distributor Discount])  as [MNett Price], 

     Remarks ,

  num( [Distributor Discount]) as LISTPRICE

FROM [INVERTER_LIST PRICE 2009 Q1 valid till March 2011.xls]

(biff, embedded labels, header is 2 lines, table is Sheet1$);

[jpapador]

I opened your QVW and PurgeChar([Distributor Discount], ' ')  as [LIST_PRICE] worked.

I have attached it.

[#7]

Hi Vegar

Thank you it work fine.

Paul

[#8]

Hi Jpa

My mistake , i add your recommended code at partial reload section. I think correct approach is to remove the space when load script and not at partial reload session.

Thank you

Paul

[#9]

Hi Ariel

I refer back to your post again , just like to info your propose script is very powerful.

Paul

[thanstad]

mid([Distributor Discount],7) as MIDPRICE

works fine as well