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

[kaushiknsolanki]

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

[Sokkorn]

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

[#4]

hi Sokkrom,

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

Regards

Sachin matta

[#5]

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

[Sokkorn]

Hi Sachin,

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

Regards,

Sokkorn

[#7]

Hi Sokkrom,

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

Regards

Sachin Matta

[Sokkorn]

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

[#9]

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

[Sokkorn]

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

[#11]

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