Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

joining two table and make it as single QVD in incremental load

Hi,

I have requirement like below.

I have two tables visits and visits_extended. Instead of creating two different QVD's in incremental load, i need to have one QVD as visits.QVD based on join condition visits.visit_key=visits_extended.visit_key


Thanks in advance

18 Replies
MK_QSL
MVP
MVP

Don't understand... What is your question...?

miguelbraga
Partner - Specialist III
Partner - Specialist III

Hi Kumar,

You must have something like this:

visits:

LOAD *

FROM [pathToQVD1];

left join

visits_extended:

LOAD *

FROM [pathToQVD2];

Store visits into [pathToIncrementalQVD];

Regards,

MB

Anonymous
Not applicable
Author

Hi Miguel,

Thanks for ur reply.

I have almost 10 columns are in common in both tables. In this will it work.

Regards,

Kumar

miguelbraga
Partner - Specialist III
Partner - Specialist III

Hey again Kumar,

This way you get rid of your sync key that generates between the two tables and you get a compressed single QVD

Have a nice job.

MB

Anonymous
Not applicable
Author

Hi MB,

As you suggested, I did some changes to my code. But it is going wrong some where. Can you look into this one

Let Vqvxpath = 'D:\QlikView\SourceDocuments\QVX\Mktg_Funnel_Prj\';

Let Vqvdpath = 'D:\QlikView\SourceDocuments\QVX\Mktg_Funnel_Reports\EOM_Bkp\New folder\';

combine:

Inserts:

LOAD *

FROM

$(Vqvxpath)dim_opportunity_inserts.qvx(qvx);

date_dim_opportunity:

LOAD Max(opportunity_record_updated_datetime) as dim_opportunity_Maxdate

Resident Inserts;

LET VDate_dim_opportunity = Peek('dim_opportunity_Maxdate',0,'date_dim_opportunity');

DROP Table date_dim_opportunity;

STORE Inserts into $(Vqvdpath)dim_opportunity.QVD(qvd);

DROP Table Inserts;

Updates:

LOAD *

FROM

$(Vqvxpath)dim_opportunity_updates.qvx(qvx)

Where (opportunity_record_updated_datetime >'$(VDate_dim_opportunity)') ;

Concatenate

LOAD *

FROM

$(Vqvdpath)dim_opportunity.qvd(qvd)

Where not Exists(opportunity_key);

STORE Updates into $(Vqvdpath)dim_opportunity.QVD(qvd);

   

Left Join

Inserts:

LOAD *

FROM

$(Vqvxpath)dim_opportunity_extended_inserts.qvx(qvx);

date_dim_opportunity_extended:

LOAD Max(opportunity_record_updated_datetime) as dim_opportunity_extended_Maxdate

Resident Inserts;

LET VDate_dim_opportunity_extended = Peek('dim_opportunity_extended_Maxdate',0,'date_dim_opportunity_extended');

DROP Table date_dim_opportunity_extended;

STORE Inserts into $(Vqvdpath)dim_opportunity_extended.QVD(qvd);

DROP Table Inserts;

Updates:

LOAD *

FROM

$(Vqvxpath)dim_opportunity_extended_updates.qvx(qvx)

Where (opportunity_record_updated_datetime >'$(VDate_dim_opportunity_extended)') ;

Concatenate

LOAD *

FROM

$(Vqvdpath)dim_opportunity_extended.qvd(qvd)

Where not Exists(opportunity_extended_key);

STORE Updates into $(Vqvdpath)dim_opportunity_extended.QVD(qvd);

 

DROP Table Updates;

STORE combine into $(Vqvdpath)dim_opportunity.QVD(qvd);

jonathandienst
Partner - Champion III
Partner - Champion III

Well, you dropped table Inserts here:

STORE Inserts into $(Vqvdpath)dim_opportunity.QVD(qvd);

DROP Table Inserts;

Then you are trying to load from the dropped table:

date_dim_opportunity_extended:

LOAD Max(opportunity_record_updated_datetime) as dim_opportunity_extended_Maxdate

Resident Inserts;


hence your error.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

Perhaps you think that tis code snippet:

Left Join

Inserts:

LOAD *

FROM

$(Vqvxpath)dim_opportunity_extended_inserts.qvx(qvx);


creates a new instance of Inserts - but the table label will be ignored because of the join qualifier above.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
miguelbraga
Partner - Specialist III
Partner - Specialist III

Drop the table only after the left join when you no longer need fields from table Inserts.

Anonymous
Not applicable
Author

Hi Jonath,

In this script I have two tables called dim_opportunity and dim_opportunity_extended. In dim_opportunity it went well. The problem occurred in dim_opportunity_extended table. If it fails in second table it could have fail in first table also. Can you correct me if I am wrong.