Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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.
Yes - you have already connected both the Code and the Calendar_table to the FACT table so connecting Code to Calendar_table with Interval_table will create a loop.
You have to remove either the Code to FACT connection or the Calendar_table to FACT connection to get rid of the circular reference.
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.
So there is no other way to eliminate the sytnethic key or huge records after implementing interval match
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.
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.
Hello Petter,
Thanks for your suggestion.
will give a try and get back soon in case of any questions
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 |
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.
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...
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