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

Linking Two Fact Tables (One Table Not Fully Loading)

asmithids — Mon, 12 Jan 2015 16:48:01 GMT

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)

jpapador — Mon, 12 Jan 2015 19:02:46 GMT

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)

jpapador — Mon, 12 Jan 2015 19:24:22 GMT

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)

asmithids — Mon, 12 Jan 2015 19:31:42 GMT

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;

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

jpapador — Mon, 12 Jan 2015 19:38:27 GMT

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;

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

asmithids — Mon, 12 Jan 2015 19:40:07 GMT

The TempTraceSalesFinal has 102,935 records.

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

jpapador — Mon, 12 Jan 2015 19:44:18 GMT

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

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

asmithids — Mon, 12 Jan 2015 19:46:29 GMT

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,