Function Help

[abhaysingh]

Hey Every one,

i need one small help,,

i have one amount field which contain values like $10000,$20000 etc..

i want to remove this $ and want's value like 10000,2000 etc...

i m trying like this but not achieving desired result...

replace(amount,'$',' ') as Amount..

Any Suggestions??

thanks

Abhay

[MK_QSL]

Your Excel file is having Custom Format that's why you can see $ sign..

You can load something as below

LOAD SNo,

     Date,

     [Voucher No],

     Branch,

     Account,

     [Contra Account],

     Num(Debit) as Debit,

     Num([Debit In Company Currency]) as [Debit In Company Currency],

     Num(Credit) as Credit,

     Num([Credit In Company Currency]) as [Credit In Company Currency],

     Num(Balance) as Balance,

     Num([Balance In Company Currency]) as [Balance In Company Currency]

FROM YOURTABLE;

[abhaysingh]

I tried to load in that way.. but it is giving me 1277893 value...every time

[MK_QSL]

check enclosed file..

[jagan · Accepted solution]

Hi,

Try this script

LOAD SNo,

     Date,

     [Voucher No],

     Branch,

     Account,

     [Contra Account],

     Num(Num#(Debit, '$#,##0.00')) AS Debit,

     Num(Num#([Debit In Company Currency], '$#,##0.00')) AS [Debit In Company Currency],

     Num(Num#(Credit, '$#,##0.00')) AS Credit,

     Num(Num#([Credit In Company Currency], '$#,##0.00')) AS [Credit In Company Currency],

     Num(KeepChar(Balance, '1234567890'), '#,##0.00') AS Balance,

     Num(KeepChar([Balance In Company Currency], '1234567890'), '#,##0.00') AS [Balance In Company Currency]

FROM

[Book1 (4).xlsx]

(ooxml, embedded labels, table is Sheet1);

Regards,

Jagan.

[stephenedberkg]

Try this

Replace( Amount,'$','')   as Amount,

[abhaysingh]

Hey jagan Mohan,,,

thanks

What is the problem, here??

[jagan]

Hi Abhay,

You have different formats in different I have scripted accordingly.

Regards,

Jagan.

[er_mohit]

Hi

Try this in your text object

=PurgeChar('$10000,$2000','$')

and the same can be used in script with mentioned number field like

PurgeChar(NumberField,'$') as Value

hope it helps

[jonathandienst]

Format the results in the same way as the source file -

LOAD SNo,

     Date,

     [Voucher No],

     Branch,

     Account,

     [Contra Account],

     Num(Debit, '# ##0.00', '.', ' ') As Debit,

     Num([Debit In Company Currency], '# ##0.00', '.', ' ') As [Debit In Company Currency], 

     Num(Credit, '# ##0.00', '.', ' ') As Credit,

     Num([Credit In Company Currency], '# ##0.00', '.', ' ') As [Credit In Company Currency],

     Num(PurgeChar(Balance, ',Cr'), '##,##,##,##,##,##0.00', '.', ',') As Balance,

     Num(PurgeChar([Balance In Company Currency], ',Cr'), '##,##,##,##,##,##0.00', '.', ',') As [Balance In Company Currency]

FROM