Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
kumarreddy257
Creator
Creator

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
Specialist III
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

kumarreddy257
Creator
Creator
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
Specialist III
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

kumarreddy257
Creator
Creator
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

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

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
Specialist III
Specialist III

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

kumarreddy257
Creator
Creator
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.