4 Replies Latest reply: Aug 14, 2015 8:15 AM by Srikanth P RSS

    Data excluded from Qvd

    Andrew Thomas


      Hi

       

      I'm trying to bring in data from 3 different tables. I've loaded the tables indiviually and sent the output to save as QVD and then I have dropped the tables. I've then bought in the 3 QVD files and joined them using a left join. It seems I'm getting the data from the first table and not the other 2.

       

      I've attached the QVD

        • Re: Data excluded from Qvd
          Srikanth P

          Please make sure that your join field values are same means case in sensitive.

          • Re: Data excluded from Qvd
            m w

            Should you be concatenating the QVDs instead of left joining?

            • Re: Data excluded from Qvd
              Jonathan Dienst

              A LEFT JOIN is a filtering join - it will only bring in the records where all the key fields match. For the second table, the key fields are

               

              AFKO_WBS,

              PRPS_WBS,

              WBS,

              CostElement,

              TotalCosts,

              MaterialCosts,

              LabourCosts,

              LabourHours,

              CostGroup,

              ValueType,

              PlanVersion

               

              Only the following are NOT key fields

              SalesActualCostsLC,

              SalesActualCostsGC,

              TradingPartner,

               

              The second join is similar.

               

              Perhaps you should  be concatenating the tables. Or possibly an Outer Join.

              • Re: Data excluded from Qvd
                Srikanth P

                According to your script, your have to concatenate these 3 tables not join because looks like all 3 tables have the same set of data granularity

                 

                In Qlikview JOIN is joining tables like SQL. Concatenate is SQL UNION type function.


                So concatenate table with Concatenate function..


                Costing_Table:

                LOAD AFKO_WBS,

                     PRPS_WBS,

                     If(AFKO_WBS <> 'NA', AFKO_WBS, PRPS_WBS) As WBS,

                     CostElement,

                     TotalCosts,

                     MaterialCosts,

                     LabourCosts,

                     LabourHours,

                     CostGroup,

                     PlannedCostsLCV0,

                     PlannedCostsLCV1,

                     PlannedCostsGCV0,

                     PlannedCostsGCV1,

                     ActualCostsLC,

                     ActualCostsGC,

                     ValueType,

                     PlanVersion

                FROM [D:\SourceDocuments\PRODQLIK\DATA\SAP DATA\CostingTableCOSP.QVD] (qvd);

                 

                 

                Concatenate(Costing_Table)

                LOAD AFKO_WBS,

                     PRPS_WBS,

                     If(AFKO_WBS <> 'NA', AFKO_WBS, PRPS_WBS) As WBS,

                     CostElement,

                     TotalCosts,

                     MaterialCosts,

                     LabourCosts,

                     LabourHours,

                     CostGroup,

                     PlannedCostsLCV0,

                     PlannedCostsLCV1,

                     PlannedCostsGCV0,

                     PlannedCostsGCV1,

                     PlannedHoursV0,

                     PlannedHoursV1,

                     ActualHours,

                     ActualCostsLC,

                     ActualCostsGC,

                     ValueType,

                     PlanVersion

                FROM [D:\SourceDocuments\PRODQLIK\DATA\SAP DATA\CostingTableCOSS.QVD](qvd);

                 

                 

                Concatenate(Costing_Table)

                LOAD AFKO_WBS,

                     PRPS_WBS,

                     If(AFKO_WBS <> 'NA', AFKO_WBS, PRPS_WBS) As WBS,

                     CostElement,

                     TotalCosts,

                     MaterialCosts,

                     LabourCosts,

                     LabourHours,

                     CostGroup,

                     SalesActualCostsLC,

                     SalesActualCostsGC,

                     TradingPartner,

                     ValueType,

                     PlanVersion

                FROM [D:\SourceDocuments\PRODQLIK\DATA\SAP DATA\CostingTableCOEP.QVD] (qvd);