Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

asmithbi
Contributor II

Linking Two Fact Tables (One Table Not Fully Loading)

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,

Tags (1)
1 Solution

Accepted Solutions
jpapador
Valued Contributor

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

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.

7 Replies
jpapador
Valued Contributor

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

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
Valued Contributor

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

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.

asmithbi
Contributor II

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

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
Valued Contributor

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

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;

asmithbi
Contributor II

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

The TempTraceSalesFinal has 102,935 records. 

jpapador
Valued Contributor

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

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

asmithbi
Contributor II

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

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,