Solved: Update/Incremental loading - Qlik Community

marleygt · ‎2015-11-20

Hi All!

I am in a big stuck creating a specific loading script, which allow me to update and insert new data on my project.

I've read almost all the discussion about Incremental loading on the board, but probably are not the right solution for my case.

Situation is the following:

I have 2 different qvd files which are considering 24 months, the first one - like 2013 and 2014

and the 2nd one 24 months like 2014 and 2015.

I need to load both qvd wihtout loosing any data from each but also to don't consider same identical data that could be present in crossing year (2014).

Must consider also that in the first qvd could be even a little part of 2015.

To help you understand more clear: qvd could contains different and unique field (present in only one of the 2 qvd) and also same identical fields.

My need is to have a single qvd (or a final loading result) with all the data I need and with no duplicity in value or fields.

For istance:

First QVD Second QVD

2013 2014 2015 2014 2015

ALPHA 12 24 3 24 18

BETA - - - 20 32

GAMMA 20 25 1 25 40

DELTA 10 15 20 - -

EPSYLON 5 10 - 6 10

Final result should be:

2013 2014 2015

ALPHA 12 24 18

BETA - 20 32

GAMMA 20 25 40

DELTA 10 15 20

EPSYLON 5 16 10

Some clarifications:

ALPHA: 2013 the only data available is "12".

2014 result must be "24" (cause data is the same in both qvd).

2015 result must be "18" (cause data is a partial or first qvd - I mean 3 pcs in 1st qvd are the same I have in 2nd qvd).

BETA: 2013 Product not consider in 1st qvd - but present only in 2nd.

2014 result must be "20" for the same reason.

2015 result must be "32" for the same reason.

GAMMA same situation as 2015 in ALPHA, (data is a partial or first qvd).

DELTA: Product not consider in 2nd qvd - but present only in 1st.

EPSYLON: last possibility, partial data in both qvd, same year (e.g. first 6 months in 1st qvd and other 6 months in 2nd qvd).

In this very moment I don't have a sample to upload, cause I've tryed directly on real project, that is very complex.

I've tryed out a very easy sample, but it's not working. So maybe, is better to ask you how might be the right script load.

However, just to give you the most information possible, the sample its like that:

QVD_DATA:

LOAD F1,

F2,

F3,

F1&F2 AS C4

FROM

[File Qvd\QVD_DATA.QVD]

(qvd);

QVD_DATA1:

LOAD F1 AS C1,

F2 AS C2,

F3 AS C3,

F1&F2 AS C4

FROM

[File Qvd\QVD_DATA1.QVD]

(qvd);

QVD_DATA:

LOAD C1,

C2,

C3,

C4

RESIDENT QVD_DATA1 where NOT exists(C4);

The only result I've reached, is to have all the data listed but not the right result.

I repeat, this is just an easy sample, but I would prefer to know first how should be the right way for writing the script which allow me to do a load like I need.

Maybe I am doing wrong the basic script.

Can you please help me on that?

Thank you very much!

Alex

maxgro · ‎2015-11-21

I made a small example with your data, .qvw is attached

This works if the key used to identify the record to read (from qvd T1 or qvd T2) is f1 (alpha, beta, ....) and year (y)

From what you said (epsylon) maybe you also need the month; in this case, the key should be

f1 - year - month

T1:

load * inline [

f1, y, v

alpha, 2013, 12

alpha,2014,24

alpha,2015,3

gamma,2013,20

gamma,2014,25

gamma,2015,1

delta,2013,10

delta,2014,15

delta,2015,20

epsylon,2013,5

epsylon,2014,10

];

store T1 into T1.qvd (qvd);

DROP Table T1;

T2:

load * inline [

f1, y, v

alpha,2014,24

alpha,2015,18

beta,2014,20

beta,2015,32

gamma,2014,25

gamma,2015,40

epsylon,2014,6

epsylon,2015,10

];

store T2 into T2.qvd (qvd);

DROP Table T2;

final:

load *, f1 & '-' & y as key, 'T1' as source

from T2.qvd (qvd);

load *, f1 & '-' & y as key, 'T2' as source

from T1.qvd (qvd)

Where not Exists(key, f1 & '-' & y);

View solution in original post

effinty2112 · ‎2015-11-20

What about:

QVD_DATA:

Load

*,

Hash128(F1 & '|' & F2 & '|' & F3 & '|' & C4) as HashID;

LOAD F1,

F2,

F3,

F1&F2 AS C4

FROM

[File Qvd\QVD_DATA.QVD]

(qvd);

//QVD_DATA1:

Load

*

Where Not Exists(HashID);

Load

*,

Hash128(F1 & '|' & F2 & '|' & F3 & '|' & C4) as HashID;

LOAD F1 AS C1,

F2 AS C2,

F3 AS C3,

F1&F2 AS C4

FROM

[File Qvd\QVD_DATA1.QVD]

(qvd);

DROP Field HashID;

maxgro · ‎2015-11-21

I made a small example with your data, .qvw is attached

This works if the key used to identify the record to read (from qvd T1 or qvd T2) is f1 (alpha, beta, ....) and year (y)

From what you said (epsylon) maybe you also need the month; in this case, the key should be

f1 - year - month

T1:

load * inline [

f1, y, v

alpha, 2013, 12

alpha,2014,24

alpha,2015,3

gamma,2013,20

gamma,2014,25

gamma,2015,1

delta,2013,10

delta,2014,15

delta,2015,20

epsylon,2013,5

epsylon,2014,10

];

store T1 into T1.qvd (qvd);

DROP Table T1;

T2:

load * inline [

f1, y, v

alpha,2014,24

alpha,2015,18

beta,2014,20

beta,2015,32

gamma,2014,25

gamma,2015,40

epsylon,2014,6

epsylon,2015,10

];

store T2 into T2.qvd (qvd);

DROP Table T2;

final:

load *, f1 & '-' & y as key, 'T1' as source

from T2.qvd (qvd);

load *, f1 & '-' & y as key, 'T2' as source

from T1.qvd (qvd)

Where not Exists(key, f1 & '-' & y);

marleygt · ‎2015-11-24

Thank you guys!

Well, Effinty2112, I've tried out your solution and - seems interesting but I am still having some errors;

I will surely keep in mind for next works.

maxgro‌ - I think your solution should work! Thank you!

I am going to test directly in my project: it will take a little for fix on the huge load script, but I am really propositive: it will work!

Need for sure to use the also the "month";

Sorry for late answer: got some trouble to handle and few time.

Thank you again!

Alex