topic Re: joining two table and make it as single QVD in incremental load in QlikView

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

Anonymous — Wed, 20 Jan 2016 10:31:56 GMT

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

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

MK_QSL — Wed, 20 Jan 2016 10:37:53 GMT

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

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

miguelbraga — Wed, 20 Jan 2016 10:45:22 GMT

Hi Kumar,

You must have something like this:

visits:

LOAD *

FROM [pathToQVD1];

left join

visits_extended:

LOAD *

FROM [pathToQVD2];

Store visits into [pathToIncrementalQVD];

Regards,

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

Anonymous — Wed, 20 Jan 2016 10:55:31 GMT

Hi Miguel,

Thanks for ur reply.

I have almost 10 columns are in common in both tables. In this will it work.

Regards,

Kumar

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

miguelbraga — Wed, 20 Jan 2016 11:00:31 GMT

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.

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

Anonymous — Wed, 20 Jan 2016 11:28:06 GMT

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

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

jonathandienst — Wed, 20 Jan 2016 11:36:12 GMT

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.

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

jonathandienst — Wed, 20 Jan 2016 11:38:18 GMT

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.

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

miguelbraga — Wed, 20 Jan 2016 11:40:24 GMT

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

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

Anonymous — Wed, 20 Jan 2016 12:14:10 GMT

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.

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

Anonymous — Wed, 20 Jan 2016 12:41:45 GMT

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.

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

jonathandienst — Wed, 20 Jan 2016 12:54:03 GMT

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.

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

Anonymous — Wed, 20 Jan 2016 12:57:34 GMT

Hi Jonath,

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

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

miguelbraga — Wed, 20 Jan 2016 12:57:35 GMT

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

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

jonathandienst — Wed, 20 Jan 2016 13:07:46 GMT

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.

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

Anonymous — Wed, 20 Jan 2016 13:10:02 GMT

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

Regards,

Kumar

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

Anonymous — Wed, 20 Jan 2016 13:14:25 GMT

Hi,

Can you see below snapshot

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

Anonymous — Wed, 20 Jan 2016 13:56:59 GMT

any ideas or suggestions on this issue.

Regards,

Kumar

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

settu_periasamy — Wed, 20 Jan 2016 14:30:19 GMT

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