Linking Two Fact Tables (One Table Not Fully Loading)

[asmithids]

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,

[jpapador]

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.

[jpapador · Accepted solution]

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.

[asmithids]

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; 

[jpapador]

On your trace sales tab, How many records does your script below produce?

TempTraceSalesFinal:

LOAD '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 TraceSales;

DROP Table TraceSales;

[asmithids]

The TempTraceSalesFinal has 102,935 records. 

[jpapador]

Ok then looking at your script above it looks like you left keep is referencing

TempInvoiceHeaderFinal

Not

InvoiceHeaderFinal

It doesnt look like the Temp version of that table has  %CustDateKey1 field

[asmithids]

jpapador,

I think you nailed it!  I removed all of the Left Keep conditions and all 102,935 records inserted into the TempTraceSalesFinal table.

Thank you!!! 

Cheers,