Help needed with Inter Record Function in load script

[#1]

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?

[rbecher]

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

[#3]

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;

[rbecher · Accepted solution]

I would suggest not to load twice (the 'closed') against 61 million rows. Instead you can load resident..

- Ralf

[disqr_rm]

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.

[#6]

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;