Interval table synthetic key

[Prabhu1204]

Interval Table:

when implementing below interval match.. It forms many synthetic keys and really concerned in adding the code table fields to the Ca lender table. The data rows increases in calender table.. Is ther any other way ?

There is no direct link between the code table and calendar table.

Calendar_table:
LEFT KEEP (FACT) LOAD DATE_KEY As DT_KEY,
Date(CAL_DATE) As (calendar_date)

FROM $(Path)DATES.qvd (qvd);

code:
LEFT KEEP (FACT) LOAD CD,
Dept_CD,
Class,
startdate,
enddate
FROM $(Path)Dates.xls

IntervalTable:
LEFT JOIN (code)
IntervalMatch(calendar_date)
LOAD startdate,enddate
RESIDENT code;

LEFT JOIN (calendar_table)
LOAD *
RESIDENT code;
DROP TABLE code;

[petter]

Be aware that an IntervalMatch() that does not have a JOIN prefix will generate a synthetic key as the way it should work. It is not a problem. It does look ugly and might be harder to understand but it works well. 

[Prabhu1204]

So there is no other way to eliminate the sytnethic key or huge records after implementing interval match

[petter · Accepted solution]

Here is an example of how to tidy things up based on the example from the Qlik Sense online documentation (https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...😞

EventLog:
LOAD * Inline [
Time, Event, Comment
00:00, 0, Start of shift 1
01:18, 1, Line stop
02:23, 2, Line restart 50%
04:15, 3, Line speed 100%
08:00, 4, Start of shift 2
11:43, 5, End of production
];

OrderLog:
LOAD AutoNumber(Start & '|' & End) AS %StartEnd,* INLINE [
Start, End, Order
01:00, 03:35, A
02:30, 07:58, B
03:04, 10:27, C
07:23, 11:43, D
];

I_:
//LEFT JOIN , not wanted here so keep 
// the IntervalMatch table as tempory I_
IntervalMatch ( Time ) 
LOAD Start, End
RESIDENT OrderLog;

Order_Event:
LOAD Time,AutoNumber(Start&'|'&End) AS %StartEnd RESIDENT I_;

DROP TABLE I_;  // Not needed anymore since it was replaced 
                // by a composite key interval table in the last step

This way you have a single link table and you will keep the row count in each of the two original tables.

[petter]

Yes - it is - I posted an example just a few seconds ago. And this should be performing well or at least much better than doing joins with huge tables.

[Prabhu1204]

Hello Petter, 

Thanks for your suggestion.

will give a try and get back soon in case of any questions

[petter]

Please click the like button or heck if the response was correct you could even mark it as a correct solution.
Believe me - it will be greatly appriciated by the contributors ...
We love likes as much as anyone posting anything on social media

[Prabhu1204]

Hello Petter,

I just tired and interval match is working great with no synthetic key.However, it is creating a circular reference loop with the other tables. All  my tables with fact tables are now linked with dotted line link

I just tired and interval match is working great with no syntetic key.However, it is creating a circular refrence loop with the other tables.

[petter]

It is hard for me to suggest any way to resolve this without seeing a screenshot of the data model (Table Viewer) or getting a list of which tables and which fields that are involved in the circular references... 

[Prabhu1204]

Calendar_table:
LEFT KEEP (FACT) LOAD DATE_KEY As DT_KEY,
Date(CAL_DATE) As (calendar_date)

FROM $(Path)DATES.qvd (qvd);

code:
LEFT KEEP (FACT) LOAD   AutoNumber(startdate& '|' & enddate) AS %StartEnd,

CD,
Dept_CD,
Class,
startdate,
enddate
FROM $(Path)Dates.xls

IntervalTable:
IntervalMatch(calendar_date)
LOAD startdate,enddate
RESIDENT code;

New:

load
calendar_date,

AutoNumber(startdate& '|' & enddate) AS %StartEnd
Aresident IntervalTable;

DROP TABLE IntervalTable