topic Re: Interval table synthetic key in QlikView

Interval table synthetic key

Prabhu1204 — Sat, 16 Nov 2024 01:22:58 GMT

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;

Re: Interval table synthetic key

petter — Fri, 31 Jan 2020 03:52:00 GMT

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.

Re: Interval table synthetic key

Prabhu1204 — Fri, 31 Jan 2020 03:56:20 GMT

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

Re: Interval table synthetic key

petter — Fri, 31 Jan 2020 03:58:06 GMT

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/ScriptPrefixes/IntervalMatch.htm😞

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.

Re: Interval table synthetic key

petter — Fri, 31 Jan 2020 04:00:02 GMT

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.

Re: Interval table synthetic key

Prabhu1204 — Fri, 31 Jan 2020 07:11:19 GMT

Hello Petter,

Thanks for your suggestion.

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

Re: Interval table synthetic key

petter — Fri, 31 Jan 2020 07:43:07 GMT

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

Re: Interval table synthetic key

Prabhu1204 — Fri, 31 Jan 2020 07:43:52 GMT

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.

Re: Interval table synthetic key

petter — Fri, 31 Jan 2020 07:48:37 GMT

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...

Re: Interval table synthetic key

Prabhu1204 — Fri, 31 Jan 2020 07:53:43 GMT

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

Re: Interval table synthetic key

Prabhu1204 — Fri, 31 Jan 2020 07:56:24 GMT

Yes, i understand..

all my linking filed from fact tables are getting dotted lines

Re: Interval table synthetic key

Prabhu1204 — Fri, 31 Jan 2020 08:16:44 GMT

excecuting this "AutoNumber(startdate& '|' & enddate) AS %StartEnd " i am getting circular reference loop

Re: Interval table synthetic key

petter — Fri, 31 Jan 2020 08:21:29 GMT

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.

Re: Interval table synthetic key

Prabhu1204 — Mon, 03 Feb 2020 04:22:36 GMT

Hello ,

When i tried to remove the code connection from fact table, i am not getting the fields selected available in the codes table .

Re: Interval table synthetic key

Prabhu1204 — Mon, 03 Feb 2020 04:24:13 GMT

Should i pull all the fileds from the code table to the interval table ?