Is it possible find total of transactions in a lookup table

[bcyates]

I have a lookup table with personal financial transactions on it. They list like they do when you review transactions from your bank. Money coming out is listed as "-$64.99" and money being deposited is listed as "$200" but in bold for example. Is it possible to find the total amount of money coming out versus the total amount being deposited.

I can search the lookup table via | inputlookup primarychecking.csv but I'm not sure if it's possible to get the total amount of money coming out of all the transactions.

[cmerriman]

|makeresults |eval data="trans=1,Amount=$152.65 trans=2,Amount=$400.87 trans=3,Amount=-$64.99 trans=4,Amount=$200.00 trans=5,Amount=-$30.00"|makemv data|mvexpand data|rename data as _raw |kv|table trans Amount
|eval Withdrawl=if(match(Amount,"^-"),Amount,0)
|eval Deposit=if(match(Amount,"^\$"),Amount,0)
|rex field=Withdrawl mode=sed "s/^[-|\$]//g"
|rex field=Deposit mode=sed "s/^[-|\$]//g"
|stats sum(Deposit) as total_deposit sum(Withdrawl) as total_withdrawl

something like this might work.

[niketn]

@bcyates, can you please add your lookup table header column and some sample data? You can mock the field values if required.

Following is one way of doing it based on information provided so far. Commands till table generate mock data and then the query creates two columns Debit and Credit for performing a total for Amount, Debit and Credit.

|  makeresults
|  eval sno="1", Amount="$200"
|  append
    [|  makeresults
|  eval sno="2", Amount="-$650"]
|  append
    [|  makeresults
|  eval sno="3", Amount="$400"]
|  append
    [|  makeresults
|  eval sno="4", Amount="-$200"]
|  append
    [|  makeresults
|  eval sno="5", Amount="$100"]
|  table sno Amount
|  eval Amount=replace(Amount,"(\$)","")
|  eval Debit=case(match(Amount,"^-"),Amount)
|  eval Credit=case(match(Amount,"^\d"),Amount)
|  addcoltotals Amount Credit Debit label="Total" labelfield="sno"

Please try out and confirm. If you want the output in any other format let us know with sample/desired output.

[bcyates]

Probably worth mentioning, the column with the transactions is titled "Amount"