QlikView App Dev

[Anonymous]

Hello,

I want to create a document in QV to be able to analyse the trades that Investor has made.

What I have is the following:

#	Market	Type	Rate	Amount	Total	Fee	Timestamp
46544268	Pair 1	Buy	2.52E-05	362.0779	0.00911	1.82E-05	27/01/2018 3:26:00 PM
46419409	Pair 1	Sell	0.00051	2	0.00102	2.04E-06	28/01/2018 9:22:00 AM
46378308	Pair 2	Buy	2E-08	398115.8	0.007962	1.59E-05	29/01/2018 11:44:00 PM
46242451	Pair 2	Sell	7E-08	9050.399	0.000634	1.27E-06	30/01/2018 10:50:00 AM
46238564	Pair 1	Sell	6.93E-06	80	0.000554	1.11E-06	30/01/2018 12:47:00 PM

What I need is to use this data to create report for each month for each pair:

What we have open and what we have closed (only buy or buy and sell).

The problem is that the buy and sell do not match the same amount, so there may be several buy records for one sell and one buy for multiple sell orders...

Also the time is different, so we have one day only buy next only sell, and some days buy and sell.

I am not sure if I have to aggregate the data in the load script, or afterwards in the sheets there is some SET analysis that can help me get what I need.

Also I will need to show how long each position was open before closing.

Any suggestions?

[Anonymous]

If possible one solution could be to end up with table with the following fields:

PairID	Buy Time	Buy Amount	Buy Price	Sell Amount	Sell Price	Sell Time	Fee
	Time of Buy order(one per buy)	Amount	Total - fee divided by Amount	Sum of all the sell orders in order to match the buy (if the buy is for 10 units and ther are 3 sells each for 4 units, it should use first two (8) + 2 from the 3rd) to get a total of 10 units	Sum of Total- fee divided by Amount	Time of last trade used	Sum of fee for buy and sell