8 Replies Latest reply: Oct 22, 2013 3:38 PM by Brian Garside RSS

    Resident Load after Inner Join...need help

    Brian Garside

      Hi I'm trying to do some ETL on some fields from previously two different tables. Both which are inner joined. That part works.

      The trouble I'm having is with adding a preceding load on top of the new Fact table that was just inner joined. How can I add this pice to the main Opportunities fact table. What did I do wrong?  adding field [dol_Contract_Value_Year1]

       

       

      Load

      custTotalValue / If( (EstCompletionDate-EstStartDate) <=365,1, Year(EstCompletionDate)-Year(EstStartDate) )   as [dol_Contract_Value_Year1]


      [Opportunities]:

      LOAD OpportunityID

           State,
      Status,
      Supervisor,
      Timescale,
      WeightedRevenue,
      Zip
      FROM
      $(vPathDirectory)Opportunity.qvd (qvd);

      Inner Join (Opportunities) //Joins on [OpportunityID]

      [Cust_Opportunities]:
      LOAD OpportunityID

           CustProposalCoordinator,
      CustPastPerformanceLead,
      CustProgramManager,
      CustStaffingPlanLead,
      CustBookBoss,
      CustWriter1,
      CustWriter2,

           CustBidType
      FROM $(vPathDirectory)OpportunityCustomTabFields.qvd
      (
      qvd);



        • Re: Nested preceding load...need help
          mayilvahanan ramasamy

          Hi

           

          I didn't see any field like that..

           

          Anyway, Try like this

           

          After joining two tables, use resident table for your calculation.

           

          Qlikview:
          First load from table, then calculate preceding load, after tat only join the another table.

           

          hope tat helps

            • Re: Preceding Load on Preceding Load...need help
              Brian Garside

              tried this but it just doubled my rows.

               

              [Opportunities]:

              LOAD OpportunityID

                 EstCompletionDate,

                 EstStartDate,

                EstCompletionDate,

                   State,
              Status,
              Supervisor,
              Timescale,
              WeightedRevenue,
              Zip
              FROM
              $(vPathDirectory)Opportunity.qvd (qvd);

              Inner Join (Opportunities) //Joins on [OpportunityID]

              [Cust_Opportunities]:
              LOAD OpportunityID

              custTotalValue,     

              CustProposalCoordinator,
              CustPastPerformanceLead,
              CustProgramManager,
              CustStaffingPlanLead,
              CustBookBoss,
              CustWriter1,
              CustWriter2,

                   CustBidType
              FROM $(vPathDirectory)OpportunityCustomTabFields.qvd
              (
              qvd);


              Concatenate Load

              custTotalValue / If( (EstCompletionDate-EstStartDate) <=365,1, Year(EstCompletionDate)-Year(EstStartDate) )   as [dol_Contract_Value_Year1]

              Resident Opportunities;

                • Re: Resident Load after Inner Join...need help
                  Prem Kumar Thangallapally

                  where are these following fields in tables?

                  custTotalValue

                  EstCompletionDate

                  EstStartDate

                  EstCompletionDate

                    • Re: Preceding Load on Preceding Load...need help
                      Brian Garside

                      Fields go to these tables; I accidently removed them to shorten the script.

                      custTotalValue = Cust_Opportunities


                      EstCompletionDate = Opportunities

                      EstStartDate = Opportunities

                      EstCompletionDate = Opportunities


                        • Re: Resident Load after Inner Join...need help
                          Prem Kumar Thangallapally

                          tab2:

                          Inner Join (Opportunities)

                          LOAD OpportunityID

                          custTotalValue,    

                          CustProposalCoordinator,
                          CustPastPerformanceLead,
                          CustProgramManager,
                          CustStaffingPlanLead,
                          CustBookBoss,
                          CustWriter1,
                          CustWriter2,

                               CustBidType
                          FROM $(vPathDirectory)OpportunityCustomTabFields.qvd
                          (
                          qvd);


                          tab3:

                          noconcatenate

                          load *,

                          custTotalValue / If( (EstCompletionDate-EstStartDate) <=365,1,Year(EstCompletionDate)-Year(EstStartDate) )   as [dol_Contract_Value_Year1]

                          resident tab2;

                          drop table tab2;

                            • Re: Preceding Load on Preceding Load...need help
                              Brian Garside

                              ok, that looks good. I ended trying two inner joins and it worked. 

                               

                              [Opportunities]:

                              LOAD OpportunityID

                                 EstCompletionDate,

                                 EstStartDate,

                                EstCompletionDate,

                                   State,
                              Status,
                              Supervisor,
                              Timescale,
                              WeightedRevenue,
                              Zip
                              FROM
                              $(vPathDirectory)Opportunity.qvd (qvd);


                              Inner Join (Opportunities) //Joins on [OpportunityID]

                              [Cust_Opportunities]:
                              LOAD OpportunityID

                              custTotalValue,    

                              CustProposalCoordinator,
                              CustPastPerformanceLead,
                              CustProgramManager,
                              CustStaffingPlanLead,
                              CustBookBoss,
                              CustWriter1,
                              CustWriter2,

                                   CustBidType
                              FROM $(vPathDirectory)OpportunityCustomTabFields.qvd
                              (
                              qvd);


                              Inner Join (Opportunities) //Joins on [OpportunityID] resulting in one Fact table

                              [dol_Contract_Value_Year1] :
                              Load OpportunityID,
                              custTotalValue / If( (EstCompletionDate-EstStartDate) <=365,1, Year(EstCompletionDate)-Year(EstStartDate) )   as [dol_Contract_Value_Year1]
                              Resident Opportunities;

                        • Re: Preceding Load on Preceding Load...need help
                          mayilvahanan ramasamy

                          Hi

                           

                          [Opportunities]:

                          LOAD OpportunityID

                             EstCompletionDate,

                             EstStartDate,

                            EstCompletionDate,

                               State,
                          Status,
                          Supervisor,
                          Timescale,
                          WeightedRevenue,
                          Zip
                          FROM
                          $(vPathDirectory)Opportunity.qvd (qvd);

                          Inner Join (Opportunities) //Joins on [OpportunityID]

                          [Cust_Opportunities]:
                          LOAD OpportunityID

                          custTotalValue,    

                          CustProposalCoordinator,
                          CustPastPerformanceLead,
                          CustProgramManager,
                          CustStaffingPlanLead,
                          CustBookBoss,
                          CustWriter1,
                          CustWriter2,

                               CustBidType
                          FROM $(vPathDirectory)OpportunityCustomTabFields.qvd
                          (
                          qvd);


                          FinalOpportunities:

                          Load

                          *,
                          custTotalValue / If( (EstCompletionDate-EstStartDate) <=365,1, Year(EstCompletionDate)-Year(EstStartDate) )   as [dol_Contract_Value_Year1]
                          Resident Opportunities;


                          Drop table Opportunities;