topic Re: Function Help in QlikView

Function Help

abhaysingh — Tue, 07 Jul 2015 05:33:15 GMT

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

Re: Function Help

jagan — Tue, 07 Jul 2015 05:36:38 GMT

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.

Re: Function Help

jagan — Tue, 07 Jul 2015 05:38:31 GMT

Hi,

You can also use Keepchar() for this

=KeepChar(FieldName, '1234567890')

Hope this helps you.

Regards,

Jagan.

Re: Function Help

MayilVahanan — Tue, 07 Jul 2015 05:40:30 GMT

Try like this

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

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

Re: Function Help

Tue, 07 Jul 2015 05:46:00 GMT

Hi abhay,

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

tis is easiest way

Re: Function Help

abhaysingh — Tue, 07 Jul 2015 05:46:52 GMT

Hi,,

thanks for revert...

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

Abhay

Re: Function Help

MayilVahanan — Tue, 07 Jul 2015 05:52:23 GMT

Can you provide sample data?

Re: Function Help

abhaysingh — Tue, 07 Jul 2015 05:52:45 GMT

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

Re: Function Help

MK_QSL — Tue, 07 Jul 2015 05:55:11 GMT

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

Re: Function Help

abhaysingh — Tue, 07 Jul 2015 06:05:43 GMT

Hey Guys,

Pls find some dummy data..

Re: Function Help

MK_QSL — Tue, 07 Jul 2015 06:10:41 GMT

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;

Re: Function Help

abhaysingh — Tue, 07 Jul 2015 06:14:10 GMT

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

Re: Function Help

MK_QSL — Tue, 07 Jul 2015 06:15:35 GMT

check enclosed file..

Re: Function Help

jagan — Tue, 07 Jul 2015 06:18:54 GMT

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.

Re: Function Help

stephenedberkg — Tue, 07 Jul 2015 06:24:45 GMT

Try this

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

Re: Function Help

abhaysingh — Tue, 07 Jul 2015 06:30:25 GMT

Hey jagan Mohan,,,

thanks

What is the problem, here??

Re: Function Help

jagan — Tue, 07 Jul 2015 06:31:38 GMT

Hi Abhay,

You have different formats in different I have scripted accordingly.

Regards,

Jagan.

Re: Function Help

er_mohit — Tue, 07 Jul 2015 06:32:22 GMT

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

Re: Function Help

jonathandienst — Tue, 07 Jul 2015 06:40:02 GMT

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