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

[Anonymous]

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

[MK_QSL]

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

[miguelbraga]

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]

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]

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]

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]

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.

[jonathandienst]

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.

[miguelbraga]

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

[Anonymous]

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.