topic Synthetic Keys and Circular References in QlikView

Synthetic Keys and Circular References

Fri, 16 Oct 2009 04:14:40 GMT

I'm making myself crazy with circular references & synthetic keys during data load. I have several tables that share fields in common. What is the best way to maintain relationships while avoiding synthetic keys?

FiscalCalendar

PeriodStartDate

PeriodEndDate

Week

Quarter

Month

Year

Partner

PartnerName

PartnerChannel

PartnerRegion

PartnerCountry

…

Product

PartNumber

PartDescription

…

SalesIn

PartnerName

PartNumber

SalesDate

PeriodEndDate

SalesQty

SalesAmount

…

SalesOut

PartnerName

PartNumber

POSDate

PeriodEndDate

POSQty

POSAmt

…

Inventory

PartnerName

PartNumber

InventoryReportDate

PeriodEndDate

InvQty

InvAmt

…

Synthetic Keys and Circular References

Anonymous — Fri, 16 Oct 2009 10:07:41 GMT

Try to concatenate your transactions tables SalesIn, SalesOut, and Inventory, into one Facts table.

Synthetic Keys and Circular References

Fri, 16 Oct 2009 20:40:10 GMT

I suppose that would do it, although that would lend another set of complexity on my dashboards vis-a-vis set analysis. Thanks anyway 🙂

Synthetic Keys and Circular References

Fri, 16 Oct 2009 20:46:35 GMT

Create one fact table made of one key using autonuberhash128 feature and the fields.

Then in your related tables, just keep the autonumberhash128 key.

Rgds,

Sébastien.

Synthetic Keys and Circular References

Anonymous — Fri, 16 Oct 2009 23:44:38 GMT

QV application design with facts tables was in use long before set analysis. And even with the set analysis - it's not the end of the world...

Synthetic Keys and Circular References

agsearle — Mon, 19 Oct 2009 19:53:07 GMT

Perhaps a bit simplistic, but if you renamed some of the fields to prevent the synthetic keys like below, and amended the load order slightly?

FiscalCalendar
PeriodStartDate
PeriodEndDate
Week
Quarter
Month
Year
...

SalesIn
PartnerName
PartNumber
SalesDate
PeriodEndDate
SalesQty
SalesAmount
…

Partner
PartnerName as PartnerNamePartner
PartnerChannel
PartnerRegion
PartnerCountry
…

Product
PartNumber
PartDescription
…

SalesOut
PartnerName
PartNumber as PartNumberSalesOut
POSDate
PeriodEndDate as PeriodEndDateSalesOut
POSQty
POSAmt
…

Inventory
PartnerName
PartNumber as PartNumberInventory
InventoryReportDate
PeriodEndDate as PeriodEndDateInventory
InvQty
InvAmt
…

Synthetic Keys and Circular References

Mon, 19 Oct 2009 22:49:36 GMT

Here's my own fix using a link table. First I started out giving all fields a unique name (omitted this step in my example).

Next, I create the complex key (shown below). Finally, I create my link table (shown below). Thanks to all for their suggestions and support!

// Add keys to fact tables
JOIN (SalesIn) LOAD
SIPartnerName,
SIPartNumber,
SIPeriodEndDate,
SIPartnerName & '|' & SIPartNumber & '|' & SIPeriodEndDate as Key
RESIDENT
SalesIn;

JOIN (POS) LOAD
POSPartnerName,
POSPartNumber,
POSPeriodEndDate,
POSPartnerName & '|' & POSPartNumber & '|' & POSPeriodEndDate as Key
RESIDENT
POS;

JOIN (Inventory) LOAD
InvPartnerName,
InvPartNumber,
InvPeriodEndDate,
InvPartnerName & '|' & InvPartNumber & '|' & InvPeriodEndDate as Key
RESIDENT
Inventory;

// =========================================================================

// Create Link table
Link:
LOAD DISTINCT
Key,
SIPartnerName & '|' & SIPartNumber & '|' & SIPeriodYr & '|' & SIPeriodMonth as MonthKey,
SIPartnerName & '|' & SIPartNumber & '|' & SIPeriodQtr as QtrKey,
SIPartnerName & '|' & SIPeriodEndDate as TrackerKey,
SIPartnerName as PartnerName,
SIPartNumber as PartNumber,
SIPeriodEndDate as PeriodEndDate
RESIDENT
SalesIn;

CONCATENATE (Link) LOAD DISTINCT
Key,
POSPartnerName & '|' & POSPartNumber & '|' & POSPeriodYr & '|' & POSPeriodMonth as MonthKey,
POSPartnerName & '|' & POSPartNumber & '|' & POSPeriodQtr as QtrKey,
POSPartnerName & '|' & POSPeriodEndDate as TrackerKey,
POSPartnerName as PartnerName,
POSPartNumber as PartNumber,
POSPeriodEndDate as PeriodEndDate
RESIDENT
POS;

CONCATENATE (Link) LOAD DISTINCT
Key,
InvPartnerName & '|' & InvPartNumber & '|' & InvPeriodYr & '|' & InvPeriodMonth as MonthKey,
InvPartnerName & '|' & InvPartNumber & '|' & InvPeriodQtr as QtrKey,
InvPartnerName & '|' & InvPeriodEndDate as TrackerKey,
InvPartnerName as PartnerName,
InvPartNumber as PartNumber,
InvPeriodEndDate as PeriodEndDate
RESIDENT
Inventory;

CONCATENATE (Link) LOAD DISTINCT
Null() & '|' & PartNumber & '|' & Null() as Key,
Null() & '|' & PartNumber & '|' & Null() as MonthKey,
Null() & '|' & PartNumber & '|' & Null() as QtrKey,
Null() & '|' & Null() as TrackerKey,
Null() as PartnerName,
PartNumber,
Null() as PeriodEndDate
RESIDENT
Product;

CONCATENATE (Link) LOAD DISTINCT
PartnerName & '|' & Null() & '|' & Null() as Key,
PartnerName & '|' & Null() & '|' & Null() as MonthKey,
PartnerName & '|' & Null() & '|' & Null() as QtrKey,
PartnerName & '|' & Null() as TrackerKey,
PartnerName,
Null() as PartNumber,
Null() as PeriodEndDate
RESIDENT
Partner;

CONCATENATE (Link) LOAD DISTINCT
Null() & '|' & Null() & '|' & PeriodEndDate as Key,
Null() & '|' & Null() & '|' & PeriodMonth as MonthKey,
Null() & '|' & Null() & '|' & PeriodQtr as QtrKey,
Null() & '|' & Null() as TrackerKey,
Null() as PartnerName,
Null() as PartNumber,
PeriodEndDate
RESIDENT
FiscalCalendar;