Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

kumarreddy257
Contributor

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

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

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

miguelbraga
Valued Contributor III

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

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

kumarreddy257
Contributor

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

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
Valued Contributor III

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

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

kumarreddy257
Contributor

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

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);

MVP
MVP

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

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
MVP
MVP

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

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
Valued Contributor III

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

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

kumarreddy257
Contributor

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

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.

Community Browser