Solved: Function Help - Qlik Community

abhaysingh · ‎2015-07-07

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

jagan · ‎2015-07-07

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.

View solution in original post

jagan · ‎2015-07-07

Hi Abhay,

Format numbers by using Num() and Num#()

Example:

LOAD

*,

Num(Num#(NumberField, '$###')) AS NumberField_Formatted

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

jagan · ‎2015-07-07

Hi,

You can also use Keepchar() for this

=KeepChar(FieldName, '1234567890')

Hope this helps you.

Regards,

Jagan.

MayilVahanan · ‎2015-07-07

Hi

Try like this

Num(PurgeChar(FieldName,'$')) as FieldName

Num(PurgeChar('$10000','$'))

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

Report Inappropriate Content · ‎2015-07-07

Hi abhay,

go to chart properties->selct concerned expresion->slct number

tis is easiest way

abhaysingh · ‎2015-07-07

Hi,,

thanks for revert...

After Doing so it is giving me only one value 1277893, by all above mention logic...

Abhay

MayilVahanan · ‎2015-07-07

hi

Can you provide sample data?

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

abhaysingh · ‎2015-07-07

Same thing i have done thru inline by making some dummy data and it's working....

does any one have idea while pulling data from excel why this is happening?

thanks

MK_QSL · ‎2015-07-07

We can't provide further help without looking your excel file. Please upload the same.

abhaysingh · ‎2015-07-07

Hey Guys,

Pls find some dummy data..

Function Help

Related Topics