18 Replies Latest reply: Jan 20, 2016 9:30 AM by Settu Periyasamy RSS

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

    kumar avisakula

      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
          Manish Kachhia

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

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

            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

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

                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
                    Miguel Braga

                    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

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

                        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
                    kumar avisakula

                    any ideas or suggestions on this issue.

                     

                    Regards,

                    Kumar

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

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