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

[Anonymous]

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.

[jonathandienst]

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.

[Anonymous]

Hi Jonath,

I am not able to understand, Can you alter my code and send it to me that would be a great help

[miguelbraga]

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

[jonathandienst]

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.

[Anonymous]

First I have to say Thanks for ur patience. I have modified like that already, but same error occurred.

Regards,

Kumar

[Anonymous]

Hi,

Can you see below snapshot

[Anonymous]

any ideas or suggestions on this issue.

Regards,

Kumar

[settu_periasamy]

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