Concatenating big qvd tables in load script editor

[jasmina_karcic]

Hi experts,

I need to concatenate 3 tables (qvd files) in load script editor.

1. finance2018 

2. finance2019

3. finance for last 13 months (actual_finance)

finance:

load * from finance2018;

concatenate (finance)

load * from finance2019;

concatenate (finance)

load * from actual_finance where date>='1.1.2020'.

This condition where make load wery slow, how can i make it faster?

Thank you

[Taoufiq_Zarra]

Hi,

I'm not expert on the subject but:

For financial history, why don't you save it in QVD instead of every time you concatenate since 2018?

financeHistory:

load * from finance2018;

load * from finance2019;

stroe financeHistory into financeHistory .qvd...

and then use financeHistory in the load

load * from financeHistory.qvd;

load * from actual_finance where date>='1.1.2020'.

if you have the same fields in the tables why you use a Forceconcatenate

these are things that can make the script faster.

[Vegar]

Try to generate a temporary table in advance containing only the dates you want.

Let's assume you name this date field [newDates]. Then you can use Where Exists instead of your IF like this.

load * from actual_finance  where exsists(newDate,date);

Exists should not break your optimizes qvd load as the if statement does. 

[jasmina_karcic]

It is already in saved in qvd... I am taking from qvd. Concatenating is not problem, problem is where conditions.. Because take much time for load... 

[jasmina_karcic]

I Will try that... 

I have tried something like that, but probably not good. Then I Will give feedback 

[Vegar]

I'm not saying that you should store to qvd, just use the exists in the load of the final QVD like this.

SET DateFormat='D.M.YYYY';
NewDates:
LOAD 
	dayname(yearstart(today()) + rowno() - 1) as NewDate
Autogenerate 
	yearend(today(),3) - yearstart(today()) +1 //Generate from 1.1 this year to 31.12 in two years
;

finance:
load * from finance2018;
concatenate (finance)
load * from finance2019;
concatenate (finance)
load * from actual_finance where exists(NewDate, date);
Drop table NewDates;

[marcus_sommer · Accepted solution]

Just some small additions. By using exists() with two parameters it won't really behave differently to a field >= value condition because it would force a processing of the data on a row-level. If exists() has only one parameter the processing happens on the column-level which has only distinct fieldvalues and the qvd-load would be optimized. This could be here reached with a slight change, like:

NewDates:
Load * where date <= today();
LOAD dayname(yearstart(today()) + rowno() - 1) as date Autogenerate yearend(today()) - yearstart(today()) +1;

finance:
load * from actual_finance where exists(date);
concatenate (finance)
load * from finance2018;
concatenate (finance)
load * from finance2019;
Drop table NewDates;

Important by this approach is to ensure that the exists-field only contained the wanted fieldvalues. For this it's usually necessary to adjust the order of the loads and/or to rename fields and/or to store and delete tables to avoid conflicts with the fields from resident-tables.

- Marcus

[jasmina_karcic]

Thank you guys, it works...

Marcus's solution cut time 4 times. It is very good. 🙂

Thank you again,

Jasmina