Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Help needed with Inter Record Function in load script

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?

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Help needed with Inter Record Function in load script

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

- Ralf

5 Replies
MVP
MVP

Help needed with Inter Record Function in load script

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

Not applicable

Help needed with Inter Record Function in load script

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;



MVP
MVP

Help needed with Inter Record Function in load script

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

- Ralf

disqr_rm
Valued Contributor III

Help needed with Inter Record Function in load script

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.

Not applicable

Help needed with Inter Record Function in load script

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;

Community Browser