topic Re: changing String value with -ve to num in QlikView

changing String value with -ve to num

Wed, 17 Jul 2013 10:13:42 GMT

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

Re: changing String value with -ve to num

Sokkorn — Wed, 17 Jul 2013 10:20:57 GMT

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

Re: changing String value with -ve to num

Wed, 17 Jul 2013 10:34:17 GMT

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

Re: changing String value with -ve to num

Sokkorn — Wed, 17 Jul 2013 10:40:15 GMT

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

Re: changing String value with -ve to num

Wed, 17 Jul 2013 10:46:18 GMT

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

Re: changing String value with -ve to num

brijesh1991 — Wed, 17 Jul 2013 10:46:50 GMT

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

Re: changing String value with -ve to num

Sokkorn — Wed, 17 Jul 2013 10:49:14 GMT

Hi Sachin,

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

Regards,

Sokkorn

Re: changing String value with -ve to num

Wed, 17 Jul 2013 11:04:55 GMT

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-NEFTICIC0000650SM321997957*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

Re: changing String value with -ve to num

Anonymous — Wed, 17 Jul 2013 11:29:50 GMT

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

NICE

Thanks

Anant

Re: changing String value with -ve to num

Wed, 17 Jul 2013 12:35:37 GMT

hi Sokkorn,

waiting for your help , please suggest

Regards

Sachin matta

Re: changing String value with -ve to num

kaushiknsolanki — Wed, 17 Jul 2013 12:40:56 GMT

Hi,

To get the last Value of the Field you can try this.

IN script :

Let vCount = noofrows('TableName');

Let vLastValue = Peek('FieldName','$(vCount)-1,'TableName');

For Dashboard.

=FieldValue(FieldName,NoOfRows(TableName))

Regards,

Kaushik Solanki

Re: changing String value with -ve to num

Sokkorn — Wed, 17 Jul 2013 12:51:10 GMT

Hi Sachin,

I'm out of office that why cannot response you on time.

Maybe you try

=Only ({1 <[Value Date]={$(=Date (Max ([Value Date],'dd-MMM-yy'))}>} [Balance])

I'm using mobile so it may missing some parentheses.

Regards,

Sokkorn

Re: changing String value with -ve to num

Thu, 18 Jul 2013 09:33:53 GMT

hi Sokkrom,

Thanks for your support but its not working please suggest something else..

Regards

Sachin matta

Re: changing String value with -ve to num

Thu, 18 Jul 2013 09:55:05 GMT

hi Kaushik ,

thanks for Reply i tried your approach and its working but actually i need to find the peek value dynamicaly

on the basis of selection.......

so i think if u can suggest to get the noof rows dynamically then it will work

Thanks in advance

Regards

Sachin

Re: changing String value with -ve to num

Sokkorn — Thu, 18 Jul 2013 10:11:09 GMT

Hi Sachin,

Can you share your sample app? So that we can figure it out together.

Regards,

Sokkorn

Re: changing String value with -ve to num

Thu, 18 Jul 2013 10:18:21 GMT

Hi Sokkrom,

please find the attached QVD, let me know if any thing else required

Regards

Sachin Matta

Re: changing String value with -ve to num

Sokkorn — Thu, 18 Jul 2013 10:57:48 GMT

Hi Sachin,

Does [Txn Date] include time or not? It hard to identify open and ending balance while we have more than one balance in same date. I guess it should have date and time.

Regards,

Sokkorn

Re: changing String value with -ve to num

Thu, 18 Jul 2013 11:11:24 GMT

Hi Sokkrom,

[TXN Date] is only a date it dont have time included,actually the CC limit of this account is 12 lakhs ,

so every day whatever credit/debit happen the balance will be calculated so here what i want to get is the

used balance that is coming daily in balance field

so my caluculation will be 12 lakhs - the last balance person get after selection

Regards

Sachin

Re: changing String value with -ve to num

Sokkorn — Thu, 18 Jul 2013 12:29:40 GMT

Hi Sachin,

I would suggest you to add new load script below:

Directory;
sahara:
LOAD date([Txn Date]) as transactionDate, 
     date([Value Date]) as clearingDate, 
     Description, 
     [Ref No./Cheque No.],
     num([Ref No./Cheque No.],'') as [Cheque No], 
     num([Branch Code],'') as [Branch Code], 
     Debit, 
     Credit, 
     num(num#(Balance,'#,##,###.00'),'#,##,###.00') as Balance//chnaging string to number  
FROM
[sahara textiles data\Sahara Textiles.xlsx]
(ooxml, embedded labels, table is [1373614284476h9Lzn6o9rCHzQxlu], filters(
Remove(Row, Pos(Top, 17)),
Remove(Row, Pos(Top, 16)),
Remove(Row, Pos(Top, 15)),
Remove(Row, Pos(Top, 14)),
Remove(Row, Pos(Top, 13)),
Remove(Row, Pos(Top, 12)),
Remove(Row, Pos(Top, 11)),
Remove(Row, Pos(Top, 10)),
Remove(Row, Pos(Top, 9)),
Remove(Row, Pos(Top, 8)),
Remove(Row, Pos(Top, 7)),
Remove(Row, Pos(Top, 6)),
Remove(Row, Pos(Top, 5)),
Remove(Row, Pos(Top, 4)),
Remove(Row, Pos(Top, 3)),
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 1))
));
[sahara2]:
NoConcatenate
LOAD RowNo() AS [_Seq],* Resident [sahara] Order By transactionDate;
DROP Table [sahara];

The reason that I create a new table called [sahara2] is to have a unique value in field [_Seq] with sort order date by transactionDate. So that we can use this field to find out open and ending balance.

So now load above script. After finish, we can find open and ending balance by:

1. Opening Balance: = Only({$<[_Seq] = {$(=Min([_Seq]))}>} Balance)

2. Ending Balance: = Only({$<[_Seq] = {$(=Max([_Seq]))}>} Balance)

Regards,

Sokkorn

Re: changing String value with -ve to num

Thu, 18 Jul 2013 14:53:42 GMT

hi Sokkorn,

its working now , thanks for your support.....

need one more help...actually i am learning Qlikview and i just started so can you please suggest me how can learn it properly with practice or some application or examples to work

Regards

Sachin