Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Load only matching records (with match from other table)

Hello,

I have a big dataset which stores price history.

[Costs]:

LOAD

  crec1_ref as [KeyPart],

  Date(Date#(Mid(9000000-num(crec1_date),2),'YYMMDD'), 'DD-MM-YYYY') as [CostDate],

  if(creccost3 = 0,creccost1, creccost2) as [Costs];

SQL SELECT *

FROM CREC_DATA

WHERE crec1_rec_type = 1

AND crec1_date < 7918769

AND crectotal_cost <> 0;

STORE [CostTemp] INTO "DATA_NEW/PartCosts.QVD";

DROP TABLE [CostTemp];

From this dataset i want to load the records which match with a table filled with stock data. Later I will join them and calculate the stock, but i already took care of that.

[Stock]:

LOAD

  [KeyPart],

  MonthEnd([StockDate])      as [CostDate],    

  [EndOfMonthStock]           

Resident [StockDetails];

Because i only need the price information of the KeyParts in the [Stock] table. Since the price database is huge, I dont want to load everything each time. It takes 40 minutes to load this, while I only need a small amount of data. What can I do to make this selection?

Tags (3)
4 Replies
Not applicable

Re: Load only matching records (with match from other table)

I tried first loading the KeyParts, then using a left join. But it still loads all 2.000.000+ records

Re: Load only matching records (with match from other table)

Besides joining you could also use a mapping-table and matching the data with applymap. But this will not really improve the loading time. Here you need a incremental load what meant that only new data or changed data will loading/matching from the database. The previous data will load from a qvd. For examples see in the manual or in help.

- Marcus

suniljain
Honored Contributor

Re: Load only matching records (with match from other table)

Please use increamental load to reduce total load time.

As for example :

QV_Table:

SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE NOT Exists(PrimaryKey);

STORE QV_Table INTO File.QVD;

Not applicable

Re: Load only matching records (with match from other table)

This database is live and gets updated every few hours. Can I use the WHERE EXISTS as a sql statement?

Community Browser