changing String value with -ve to num

[#1]

Hi All,

i have a table which have a field by name as Balanace and value for this is -8,00,000.34 , so qlikview is taking this as string when i load the data from Excel

i used num and num# function but its not working so please help

[Sokkorn · Accepted solution]

Hi,

Not sure you Num#() like this yet

=Num(Num#('-8,00,000.34','#,##,###.00'),'#,###,###.00') -> Return -800,000.34

I found that you have this format #,##,###.00

So in your load script should be

[Data]:

LOAD

     ...

    Num(Num#(Balance,'#,##,###.00'),'#,###,###.00')     AS [Bal],

     ...

From...

Regards,

Sokkorn

[#3]

Hi Sokkron,

its working fine thanks for your support but can u plehase explain why we need to use num(num#) as it think it should work with only Num# also

Regards

Sachin

[Sokkorn]

Hi Sachin,

FYI,

Num#(expression [ , format-code[ , decimal-sep [ , thousands-sep] ] ])

The Num# function evaluates the expression numerically according to the string given as format-code. Decimal separator and thousands separator can be set as third and fourth parameters. If the parameters 2-4 are omitted, the default number format set by script variables or in the operating system is used.

This mean that we translate from string format into number formate. Ex. we have value -8,00,000.34 is not a proper number format. So that we need to use Num# with specific format to our string value and then Num# will translate those into number.

Regards,

Sokkorn

[#5]

Thanks Sokkrom,

i need a one more help here if u can

actually i need to find a last value of a balance column after and before the selection of data , so please suggest

let say i have a different balance amount for every day but here i need the last balance amount that is coming on last day or last day after selection ..... i tried

Bottom({$} Balance)...but its not working

Regards

Sachin

[brijesh1991]

In your script, use this as one field:    Num(Num#(Balance,'#,##,###.00'),'#,###,###.00')  As Balance

[Sokkorn]

Hi Sachin,

How about Max(Balance)? Did you try yet?

Regards,

Sokkorn

[#8]

hi Sokkrom,

i dont need a max of the balance actually just required the last value of balance column (with and without selection) whether it min or max of all other balance amount

Txn Date	Value Date	Description	Ref No./Cheque No.	Branch Code	Debit	Credit	Balance
7-Jun-13	7-Jun-13	CREDIT-MiscCredit BY-30748125336--		7959		5,406.00	-8,61,825.34
10-Jun-13	10-Jun-13	BY TRANSFER-NEFT*ICIC0000650*SM321997957*SANJAY TEXTILES--	TRANSFER FROM 3199680044308	4430		70,159.00	-7,91,666.34
10-Jun-13	10-Jun-13	CASH DEPOSIT-CASH DEPOSIT SELF--		30020		20,000.00	-7,71,666.34
10-Jun-13	10-Jun-13	INTER CITY CHARGES---38976288	38976288	30020	40		-7,71,706.34
10-Jun-13	10-Jun-13	TO DEBIT THROUGH CHEQUE-clg cc--174901	174901	1620	23,494.00		-7,95,200.34
11-Jun-13	12-Jun-13	BY CLEARING / CHEQUE-HDF--101173	101173	1620		36,094.00	-7,59,106.34
11-Jun-13	12-Jun-13	BY CLEARING / CHEQUE-PNB--96863	96863	1620		40,000.00	-7,19,106.34
11-Jun-13	12-Jun-13	BY CLEARING / CHEQUE-PNB--95714	95714	1620		40,000.00	-6,79,106.34
11-Jun-13	11-Jun-13	CHQ RET CHARGES---174902	174902	1620	102		-6,79,208.34
12-Jun-13	12-Jun-13	CASH CHEQUE-Paid to AMIT--174912	174912	1620	30,000.00		-7,09,208.34
12-Jun-13	12-Jun-13	TO DEBIT THROUGH CHEQUE---174913	174913	1620	44,202.00		-7,53,410.34
12-Jun-13	12-Jun-13	TO DEBIT THROUGH CHEQUE---174914	174914	1620	19,828.00		-7,73,238.34
13-Jun-1day3	13-Jun-13	CASH CHEQUE-Paid to SAHIL--174917	174917	1620	20,000.00		-7,93,238.34

so above every day i have a new balance so want to caluculate opening and closing balance so there fore i need a balance day wise

[Anonymous]

Thanks !! i was looking to understand the diffrence between num and num#

NICE

Thanks

Anant

[#10]

hi Sokkorn,

waiting for your help , please suggest

Regards

Sachin matta