2 Replies Latest reply: Oct 2, 2014 4:15 PM by Ankur Akash RSS

    Scenario: Getting error when combining QVDs using combination of left join and concatenate

    Ankur Akash

      Hi,

       

      QVD and Tables:

      I have all the QVDs for associated tables that I wanted to use in my application.

      I have one fact table (Trans).

      I have two additional tables:

      a) Sig

      b) Payer

       

      The SQl query is:

      SELECT COUNT(*),TO_CHAR(TO_DATE(CREATE_DATE),'MON-YYYY')

      FROM SIG SA ,PAYER PT

      WHERE TRUNC(SA.CREATE_DATE) BETWEEN '01-jan-2014' AND '31-mar-2014'

      AND SA.PAYER_NO=PT.PAYER_NO

      GROUP BY TO_CHAR(TO_DATE(SA.CREATE_DATE),'MON-YYYY')

      What is required?
      I have to create a bar chart which would be based on two tables SIG and Payer.

      What I have done so far:

      Loaded QVD "Sig". Doing left join with QVD "Payer". Creating a new table "SIG_Payer" which would be a combination of QVDs(SIG and Payer). And then concatenating it with fact table "Trans".

      The example code is mentioned below:


      Sig:

      LOAD

           PAYER_NO,

           UID,

           CREATE_DATE,

           Month(CREATE_DATE) as Sig_Month,

           Year (CREATE_DATE) as Sig_Year,

           's' as FLAG

      From ../(QVD);

       

      left join (Sig)

       

      Payer:

      LOAD   

          if(CENTRE_NO=1,'A',if(CENTRE_NO=2,'B'))  as TaxCentreNo,

          PAYER_NO

           //CENTRE_NO

      From: //  (QVD); 

       

       

      Concatenate (Trans) 

       

      Payer_Sig:

      LOAD PAYER_NO,

           UID,

           CREATE_DATE,

           Month(CREATE_DATE) as Sig_Month,

           Year (CREATE_DATE) as Sig_Year,

           's' as FLAG,

           if(CENTRE_NO=1,'A',if(CENTRE_NO=2,'B'))  as TaxCentreNo

      Resident

      Sig;

       

      Drop table Sig;

       

       

       

      Here, Trans(fact table) is having field PAYER_NO and has field CENTRE_NO in similar format as of QVD Payer.

       

      Issue:

      When I reload it throws an error (Trans) field "TaxCentreNo" not found. Though this field is available in both fact table and newly created "Sig_Payer" table (I already added fields of "Payer" using left join with "Sig" in "Sig".)

       

       

      Request your Step by Step Resolution:

      Your guidelines in this regard is much appreciated.

       

      Regards..