topic Re: Executing script takes to long in QlikView

Executing script takes to long

Fri, 27 May 2016 09:28:02 GMT

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

Re: Executing script takes to long

PrashantSangle — Fri, 27 May 2016 09:35:13 GMT

Remove synthetic key 10+ are too much for 3-4 million records.

Regards

Re: Executing script takes to long

Peter_Cammaert — Fri, 27 May 2016 09:35:14 GMT

There is a very easy technique to shorten the delay to reload your documents: eliminate the synthetic keys. If you scan the tilmestamps in the log and compare them with the actual run time, you will see that creating synthetic keys for milliions of rows will take an inordinate amount of time. That time is wasted.

Best,

Peter

Re: Executing script takes to long

Fri, 27 May 2016 09:40:16 GMT

Yeah, How do I remove them, and how would I perform joins in the app?

Re: Executing script takes to long

PrashantSangle — Fri, 27 May 2016 09:42:28 GMT

Hi,

Read article about link table, Composite key

Give you better idea about solution.

Regards

Re: Executing script takes to long

Peter_Cammaert — Fri, 27 May 2016 09:42:58 GMT

Synthetic keys can be easily removed by creating so-called "composite keys". Concatenate all key values into a single field in both tables (and rename/remove the originals) will reduce the association values between tables to a single one in each case. The synthetic keys will go away.

Peter

Re: Executing script takes to long

PrashantSangle — Fri, 27 May 2016 09:44:01 GMT

or you can post your script.

then we can help to optimize it.

Regards

Re: Executing script takes to long

Fri, 27 May 2016 09:53:36 GMT

I posted it, in the main. This will be very useful for me if you can help em with this issue.

Re: Executing script takes to long

PrashantSangle — Fri, 27 May 2016 09:56:16 GMT

Hi,

I am working on personal edition so it will be better for me if you copy and paste your entire script in one doc and attached doc here.

or you can paste your script here.

Regards

Re: Executing script takes to long

Fri, 27 May 2016 10:01:04 GMT

Hi,

For removing synthetic keys, see below links.

http://www.analyticsvidhya.com/blog/2014/12/remove-synthetic-key-concatenation-link-table-qlikview/

http://www.learnallbi.com/qlikview-resolving-synthetic-keys/

https://community.qlik.com/thread/27662

http://www.learnallbi.com/link-table-in-qlikview/

-Joyson G

Re: Executing script takes to long

Fri, 27 May 2016 10:07:19 GMT

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

Re: Executing script takes to long

PrashantSangle — Fri, 27 May 2016 10:23:13 GMT

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

Re: Executing script takes to long

kkkumar82 — Fri, 27 May 2016 10:23:29 GMT

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.

Re: Executing script takes to long

maniram23 — Fri, 27 May 2016 10:44:52 GMT

HI,

Please find the attached word document file

Re: Executing script takes to long

Fri, 27 May 2016 10:58:21 GMT

Field not found <AccountID>

Re: Executing script takes to long

Fri, 27 May 2016 11:05:34 GMT

Thank you so much!

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