Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Don't understand... What is your question...?
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
Hi Miguel,
Thanks for ur reply.
I have almost 10 columns are in common in both tables. In this will it work.
Regards,
Kumar
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
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);
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.
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.
Drop the table only after the left join when you no longer need fields from table Inserts.
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.