Executing script takes to long

[#1]

Hello community,

I have a problem. I am reading from 4 different QVDs 3-4 millions in each QVD. Qlikview is performing joins and creating synthetic keys. It takes far to long to execute the script over 15minutes creating 10+ synthetic keys. I dont think it should take this long. Is there anyway to speed up the process? It's currently QVD absed on Year, I need it this way.

Meddelandet redigerades av: Niklas Söderqvist

[#2]

OK! I ahve uplaoded a word document for you. Thanks!:D

[PrashantSangle]

Hi

try below code,

Analysis:
LOAD TradeDate,
AccountId,
MarketId,
ExecutionVenueId,
ValueName,
CurrencyId,
Value,
ValueSEK
FROM

(qvd);

Accounts:
LOAD AccountId,

AccountID &’-’& TradeID as AccountTradeKey,
ClientId,
AccountShortName,
AccountLongName,
ParentAccountId,
Type,
CountryISOCode,
City,
Manager,
ManagerId,
Trader,
TraderId as Accounts_TraderID,
RegisteredDate,
DeregisteredDate,
Sales,
SalesId,
BackupTrader,
BackupTraderId
FROM

(qvd);

Notes:
LOAD AccountDate,
AccountId,
Cancellation,
Commission,
CSA,
CurrencyId as Notes_CurrencyId,
IsBuy as Notes_IsBuy,
ISIN as Notes_ISIN,
MarketId,
NoteId,
Price as Notes_Price,
SettleDate,
ShortName as Notes_ShortName,
TradeDate as Notes_TradeDat,
Volume as Notes_Volume
FROM

(qvd);

Trades:
LOAD AccountId as Trade_AccountId,

AccountID &’-’& TradeID as AccountTradeKey,
AccountRef,
ClientId,
CounterpartId,
CurrencyId as Trades_CurrencyId,
ExecutionVenueId,
IsBuy as Trade_IsBuy,
ISIN as Trade_ISIN,
LiquidityIndicator,
MarketOrderId,
OrderbookId,
OrderId as Trade_OrderId,
Price as Trade_Price,
ShortName as Trades_ShortName,
TradeDate as Trades_TradeDate,
TradeId as Trade_TradeId,
Volume as Trades_Volume
FROM

(qvd);

Just Confirm it that you still have synthetic key or not.

then create composite key as I have created and rename common field.

Regards

[kkkumar82]

Hi Niklas,

If you know how the tables are related to each other by IDs

in each of the tables write the combinations using composite keys or even an auto number of the composite key to speed up your loading time.

for eg

Table1:

Load

key1,

Key2,

Key3,

Key1 &'-' & Key2 as %Key1,

Key2 &'-' & Key3 as %key2

from table1;

Table2:

Load

key1,

Key2,

Key1 &'-' & Key2 as %Key1

from table2;

Table3:

Load

key2,

Key3,

Key2 &'-' & Key3 as %Key2

from table3;

If you observe table 1 will now be linked with table 2 and table 3 with the keys %key1 and %key2 and no synthetic keys

either.

Hope it helps you.

[maniram23 · Accepted solution]

HI,

Please find the attached word document file

[#6]

Field not found <AccountID>

[#7]

Thank you so much!

This works perfect. What did you do how's the mindset while doing this?