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
Hi Miguel,
problem is only after doing Left Join only. Before that the first table got loaded successfully, when loading second second table the problem is occurring.
Kumar Reddy wrote:
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.
No - the drop table occurs AFTER dim_opportunity (so it exists) and before dim_opportunity_extended (so it no longer exists).
There is no problem with the LOAD syntax, just the absence of the source for the second one.
It has nothing to do with the join.
Hi Jonath,
I am not able to understand, Can you alter my code and send it to me that would be a great help
Try this:
Let Vqvxpath = 'D:\QlikView\SourceDocuments\QVX\Mktg_Funnel_Prj\';
Let Vqvdpath = 'D:\QlikView\SourceDocuments\QVX\Mktg_Funnel_Reports\EOM_Bkp\New folder\';
combine:
Inserts1:
LOAD *
FROM
$(Vqvxpath)dim_opportunity_inserts.qvx(qvx);
date_dim_opportunity:
LOAD Max(opportunity_record_updated_datetime) as dim_opportunity_Maxdate
Resident Inserts1;
LET VDate_dim_opportunity = Peek('dim_opportunity_Maxdate',0,'date_dim_opportunity');
DROP Table date_dim_opportunity;
STORE Inserts1 into $(Vqvdpath)dim_opportunity.QVD(qvd);
DROP Table Inserts1;
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
Inserts2:
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 Inserts2;
LET VDate_dim_opportunity_extended = Peek('dim_opportunity_extended_Maxdate',0,'date_dim_opportunity_extended');
DROP Table date_dim_opportunity_extended;
STORE Inserts2 into $(Vqvdpath)dim_opportunity_extended.QVD(qvd);
DROP Table Inserts2;
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);
Miguel Braga wrote:
Try this:
<snip>
Left Join
Inserts2:
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 Inserts2;
As I said before - the Left join statement will cause the label (Inserts2) to be ignored and the load will be joined to the preceding table. So you will get the same error as before.
I think you need to step back and think very clearly what you are trying to achieve and perhaps try with a simpler example with less happening.
First I have to say Thanks for ur patience. I have modified like that already, but same error occurred.
Regards,
Kumar
Hi,
Can you see below snapshot
any ideas or suggestions on this issue.
Regards,
Kumar
Hi,
May be try this
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);
DROP Table Updates;
//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;
Combine:
LOAD * From $(Vqvdpath)dim_opportunity.QVD(qvd);
Left Join(Combine) //Not sure how about your key fields
LOAD * From $(Vqvdpath)dim_opportunity_extended.QVD(qvd);
STORE Combine into $(Vqvdpath)dim_opportunity_Final.QVD(qvd);