Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying (in vain) to create a derived field, as part of the load process, which contains the '0' or '1' value (which is subsequently used as a sum function in the chart expression)
The following table lists two trades that were created on the 1/2/2011. Both trades had amendments on the 2/2/2011 and one of the trades was closed on the 3/2/2011. The objective of the exercise is to determine how many of the trades are still Open.
This is how the data looks: (Table name for the purpose of this explanation = tblTest_OPEN_trades)
Trans Action _id | main_ trade _ref | Trans action _status | Trade_ created _on | Updated_ Trans action _received _on |
16 | TER - 10 | Open | 01-Feb-11 | 01-Feb-11 |
21 | TER - 10 | Open | 01-Feb-11 | 02-Feb-11 |
15 | XYZ - 10 | Open | 01-Feb-11 | 01-Feb-11 |
20 | XYZ - 10 | Open | 01-Feb-11 | 02-Feb-11 |
25 | XYZ - 10 | Closed | 01-Feb-11 | 03-Feb-11 |
And this is how I want the result to look: (once created, the data in the table below is saved as a QVD)
Trans Action _id | main_ trade _ref | Trans action _status | Trade_ created _on | Updated_ Trans action _received _on | qv_ open _DIST |
16 | TER - 10 | Open | 01-Feb-11 | 01-Feb-11 | 0 |
21 | TER - 10 | Open | 01-Feb-11 | 02-Feb-11 | 1 |
15 | XYZ - 10 | Open | 01-Feb-11 | 01-Feb-11 | 0 |
20 | XYZ - 10 | Open | 01-Feb-11 | 02-Feb-11 | 0 |
25 | XYZ - 10 | Closed | 01-Feb-11 | 03-Feb-11 | 0 |
So, GROUPING on [main_trade_ref), if MAX([Updated_transaction_received_on]=Closed, set a derived field called [qv_open_DIST] for all transactions to '0', OTHERWISE set [qv_open_DIST] for the MAX([Updated_transaction_received_on] to '1' (all other iterations for the trade should be set to '0')
Once I have the required value either '0' or'1' in the [qv_open_DIST] field, I simply create a chart and add sum([qv_open_DIST]) as the expression.
This is the code so far:
Xtrades_Current:
SQL SELECT *
FROM `tblTest_OPEN_trades`; // This refers to the initial table
LEFT JOIN (Xtrades_Current)
Load transaction_id,
main_trade_ref,
transaction_status,
if(peek('main_trade_ref')=main_trade_ref,0,1) as qv_open_DIST
resident Xtrades_Current
order by main_trade_ref, updated_transaction_received_on DESC;
Can anybody help?
I would suggest not to load twice (the 'closed') against 61 million rows. Instead you can load resident..
- Ralf
Hi,
I think this is the wrong approach. It is more easy to aggregate the amount of open trades after the load like this:
tblAggr:
LOAD count(DISTINCT main_trade_ref) as cnt_trades
Resident tblTest_Open_Trades;
LOAD count(DISTINCT main_trade_ref) as cnt_trades
Resident tblTest_Open_Trade
Where Transaction_status='Closed';
SET vCntOpen = peek('cnt_trades', 0, 'tblAggr') - peek('cnt_trades', 1, 'tblAggr');
Drop table tblAggr;
Or the other way around: LEFT JOIN on closed trades with value 1 and than calculate
count(DISTINCTmain_trade_ref) - sum(qv_closed_DIST)
- Ralf
Thanks Ralf,
I had the added complication of having to resolve this issue by adding/blending into an existing part in the load file script. After further testing, I think I have figured out the solution, as below, I now need to apply this against 61million rows of data and check for anomolies. Thank you very much for taking the time to assist.
Xtrades_Current:
SQL SELECT *
FROM `tblTest_OPEN_trades`;
Closed_trades:
Load main_trade_ref as closed_main_trade_ref;
SQL SELECT *
FROM `tblTest_OPEN_trades`
where transaction_status='Closed';
LEFT JOIN (Xtrades_Current)
load transaction_id,
main_trade_ref,
if(exists(closed_main_trade_ref,main_trade_ref),0,if(peek('main_trade_ref')=main_trade_ref,0,1)) as qv_open_DIST,
resident Xtrades_Current
order by main_trade_ref, updated_transaction_received_on DESC;
I would suggest not to load twice (the 'closed') against 61 million rows. Instead you can load resident..
- Ralf
Or you may store all the records in a temporary QVD file, and then load from it. Something like this:
Xtrades_Current:
SQL SELECT *
FROM `tblTest_OPEN_trades`;
STORE Xtrades_Current into Temp.qvd;
tmp:
LOAD * INLINE [
transaction_status
Closed
];
Closed_trades:
Load main_trade_ref as closed_main_trade_ref
FROM Temp.qvd (qvd)
WHERE Exists (transaction_status)
DROP Table tmp;
After this you may drop that temporary QVD file as well.
Thanks Ralf & Rakesh,
I have taken the advice from both of you on board and I believe the following code, reflects both of your suggestions - would you mind taking a quick look:
Xtrades_Current:
SQL SELECT *
FROM `tblTest_OPEN_trades`;
STORE Xtrades_Current into Temp.qvd;
Closed_trades:
Load main_trade_ref as closed_main_trade_ref
FROM TEMP.qvd(qvd)
WHERE transaction_status='Closed';
LEFT JOIN (Xtrades_Current)
load transaction_id,
main_trade_ref,
if(exists(closed_main_trade_ref,main_trade_ref),0,if(peek('main_trade_ref')=main_trade_ref,0,1)) as qv_open_DIST,
resident Xtrades_Current
order by main_trade_ref, updated_transaction_received_on DESC;
drop table Closed_trades;