7 Replies Latest reply: Jan 12, 2015 2:46 PM by Alec Smith RSS

    Linking Two Fact Tables (One Table Not Fully Loading)

    Alec Smith

      Hello,

       

      I created the attached qvw linking two fact tables.  The issue I am having is that the "TraceSalesFinal" table in the qvw (sourced from the attached spreadsheet) is not loading all of the records from the spreadsheet.  The "TraceSalesFinal" table is loading 1,705 records when it needs to load 102,935 records.  I tried using the Left Keep(TempInvoiceHeaderFinal)  function to pull all of the records from the spreadsheet, but its not working.  The issue seems to be my joins across the tables, but I not seeing where the issue is.

       

      I would very much appreciate any assistance with this issue.

      Thank you in advance!

      Regards,

        • Re: Linking Two Fact Tables (One Table Not Fully Loading)
          Jared Papador

          What is your key field you are using between TraceSalesFinal and Left Keep(TempInvoiceHeaderFinal).

           

          Left Keep will only load records from TraceSalesFinal that it finds matches for in TempInvoiceHeaderFinal.  You still need to have a key between the two.

            • Re: Linking Two Fact Tables (One Table Not Fully Loading)
              Jared Papador

              Looking at your very first left keep it looks like you have a synthetic key between TempTraceSalesFinal and TempInvoiceHeaderFinal.

               

              What that means for the keep is that it will only bring records in from the spreadsheet where ALL the records in the following fields match.

              CustKey,

              Year,

              Month,

              MapZip

               

              Not sure if that's what you intended.

              • Re: Linking Two Fact Tables (One Table Not Fully Loading)
                Alec Smith

                Hi jpapador,

                 

                Thank you for responding.  I am using the following code to generate a key and linked table between the two tables.  The Hash128(CustKey,Year,Month) As %CustDateKey1 script in both tables creates the key.

                 

                InvoiceHeaderFinal:
                Load Hash128(CustKey,Year,Month) As %CustDateKey1,
                'Distributor'
                As CustType,
                AuthOvrdAmt,
                Balance,
                InvoiceAmt,
                InvoiceCmnt,
                InvoiceDate,
                PostDate,
                //Year,
                //Month,
                SalesAmt,
                ShipAmt,
                ShipToAddrCity,
                //MapZip,
                InvcKey,
                CustKey,
                TranID,
                TranType,
                TranTypeDesc
                Resident TempInvoiceHeaderFinal;

                Left Keep(TempInvoiceHeaderFinal)

                TraceSalesFinal:
                Load Hash128(CustKey,Year,Month) As %CustDateKey1,
                'Customer'
                As ECustType,
                //CustKey,
                //Year,
                //Month,
                EInvoice,
                Distributor,
                DistributorName,
                GLN,
                HIN,
                CustomerNumber,
                CustomerName,
                EAddress,
                ECity,
                EState,
                //MapZip,
                ItemKey,
                EQuantity,
                EUnitCost,
                EExtCost,
                GPO,
                IDN,
                ContractNumber
                Resident TempTraceSalesFinal;


                GeoLinkTable:
                Load  Hash128(CustKey,Year,Month) As %CustDateKey1, MapZip, Year, Month Resident TempInvoiceHeaderFinal;
                Concatenate
                Load  Hash128(CustKey,Year,Month) As %CustDateKey1, MapZip, Year, Month Resident TempTraceSalesFinal;
                DROP Table TempInvoiceHeaderFinal,TempTraceSalesFinal;