Fact Tables Not Connected Properly / Synthetic Keys in Link Table

Alihan_02 — Tue, 11 Feb 2025 13:42:39 GMT

[EventsForCalendar_temp]: // <--- NEUER Tabellenname hier LOAD subject, start_dateTime, start_timeZone as [EventsForCalendar.start_timeZone], end_dateTime as [EventsForCalendar.end_dateTime], end_timeZone as [EventsForCalendar.end_timeZone], eventId as [EventsForCalendar.eventId], attendees as [EventsForCalendar.attendees], SubField(attendees, ',') AS 'Teilnehmer', Lower(SubField(attendees, ',')) AS Key, location_displayName as [EventsForCalendar.location_displayName], importance as [EventsForCalendar.importance], body_preview as [EventsForCalendar.body_preview], recurrence as [EventsForCalendar.recurrence], categories as [EventsForCalendar.categories], hasAttachments as [EventsForCalendar.hasAttachments], webLink as [EventsForCalendar.webLink], body_contentType as [EventsForCalendar.body_contentType], body_content as [EventsForCalendar.body_content], ConvertToLocalTime([start_dateTime], 'CET', false()) as Local_start_dateTime, Date(Floor(ConvertToLocalTime([start_dateTime], 'CET', false())), 'YYYY-MM-DD') AS Startdatum, If(Floor(ConvertToLocalTime([start_dateTime], 'CET', false())) < Today(), 1, 0) AS IsBeforeCutoff, Time(ConvertToLocalTime([start_dateTime], 'CET', false()), 'hh:mm') AS Startzeit, ConvertToLocalTime([end_dateTime], 'CET', false()) as Local_end_dateTime, Date(Floor(ConvertToLocalTime([end_dateTime], 'CET', false())), 'YYYY-MM-DD') AS Enddatum, Time(ConvertToLocalTime([end_dateTime], 'CET', false()), 'hh:mm') AS Endzeit, WeekDay(ConvertToLocalTime([start_dateTime], 'CET', false())) AS Debug_WeekDay2, If([end_dateTime] >= Today(), Dual('Anstehende Termine', 1), Dual('Vergangene Termine', 0)) AS Termine, // Flag für anstehende Termine Trim(SubField(subject, '/', 1)) AS Kunde, Trim(SubField(subject, '/', 2)) AS Ort, Trim(SubField(subject, '/', 3)) AS Kategorie, Trim(SubField(subject, '/', 4)) AS Produktgruppe, // Hier wird das '#' entfernt Trim(Replace(SubField(subject, '/', 5), '#', '')) AS Kundennummer, // Gleiches auch mit _Key_Kunde da ich kundennummer nicht zugreifen kann, qlik verarbeitet alles in dem moment '1_' & Trim(SubField(subject, '/', 4)) & '_' & Trim(Replace(SubField(subject, '/', 5), '#', '')) AS _Key_Kunde, Trim(SubField(subject, '/', 6)) AS Terminart, Trim(SubField(subject, '/', 7)) AS Kollektionsquartal, Trim(Replace(SubField(subject, '/', 5), '#', '')) & '_' & Trim(SubField(subject, '/', 4)) AS %Key_Field, ApplyMap('KategorieMapping', Lower(Trim( Trim(SubField(subject, '/', 3)))), Upper(Trim( Trim(SubField(subject, '/', 3))))) as KategorieStandard WHERE WildMatch(Trim(SubField(subject, '/', 6)), 'S1', 'RT', 'OT') ; // nach Terminart wenn Struktur vielleicht geändert wird dann auf 6 setzen SELECT subject, start_dateTime, start_timeZone, end_dateTime, end_timeZone, eventId, attendees, location_displayName, importance, body_preview, recurrence, categories, hasAttachments, webLink, body_contentType, body_content FROM EventsForCalendar WITH PROPERTIES ( WITH PROPERTIES ( calendarID='REDACTED', maxResults='' ); LIB CONNECT TO 'MyDatabaseConnection'; [int_ausap]: LOAD Datum_Anfangstermin, Datum_Auftragseingang, Datum_Storno, Saison, Trim(Storno) as Storno, Anzahl_Bestellt, Anzahl_Fakturiert, Kundennummer as Kundennummer, Produktgruppe as Produktgruppe, Kundennummer & '_' & Produktgruppe as %Key_Field ; // Storno Feld ergänzen, Neue Mapping SQL SELECT Datum_Anfangstermin, Datum_Auftragseingang, Datum_Storno, Saison, Storno, SUM(Anzahl_Bestellt) AS Anzahl_Bestellt, SUM(Anzahl_Fakturiert) AS Anzahl_Fakturiert, Produktgruppe, Kundennummer FROM my_database.PROD.int_ausap WHERE CAST(SUBSTR(Saison, 3) AS INT64) >= (EXTRACT(YEAR FROM CURRENT_DATE()) - 2000 - 1) GROUP BY _Key_Kunde, Datum_Anfangstermin, Datum_Auftragseingang, Datum_Storno, Saison, Storno, Kundennummer, Produktgruppe ; [EventsForCalendar]: Load *, If (Produktgruppe = 'alle', ApplyMap('PgMapping', Upper(Trim(KategorieStandard)), (Trim(Produktgruppe))) , Produktgruppe) as Produktgruppe_alle Resident EventsForCalendar_temp; LinkTable: Load Distinct [%Key_Field], SubField(%Key_Field, '_', 1) AS Kundennummer, // Extrahiert Kundennummer SubField(%Key_Field, '_', 2) AS Original_Produktgruppe, // Nimmt den ursprünglichen Produktgruppenwert SubField(SubField(%Key_Field, '_', 2), ' ') AS Produktgruppe // Trennt Mehrfachwerte (z.B. "91 92") Resident EventsForCalendar_temp; //1 Concatenate(LinkTable) Load Distinct [%Key_Field], SubField(%Key_Field, '_', 1) AS Kundennummer, // Extrahiert Kundennummer SubField(%Key_Field, '_', 2) AS Original_Produktgruppe, // Nimmt den ursprünglichen Produktgruppenwert SubField(SubField(%Key_Field, '_', 2), ' ') AS Produktgruppe // Trennt Mehrfachwerte (z.B. "91 92") // Trennt Mehrfachwerte, Resident int_ausap; // Falls [EventsForCalendar] diese Felder enthält, kannst du sie entfernen: Drop Fields %Key_Field From [EventsForCalendar]; Drop Fields %Key_Field From [int_ausap]; //Drop Fields Kundennummer, Produktgruppe From [EventsForCalendar]; //Drop Fields Kundennummer, Produktgruppe From [int_ausap]; Drop Table EventsForCalendar_temp;

[EventsForCalendar_temp]: // <--- NEUER Tabellenname hier LOAD subject, start_dateTime, start_timeZone as [EventsForCalendar.start_timeZone], end_dateTime as [EventsForCalendar.end_dateTime], end_timeZone as [EventsForCalendar.end_timeZone], eventId as [EventsForCalendar.eventId], attendees as [EventsForCalendar.attendees], SubField(attendees, ',') AS 'Teilnehmer', Lower(SubField(attendees, ',')) AS Key, location_displayName as [EventsForCalendar.location_displayName], importance as [EventsForCalendar.importance], body_preview as [EventsForCalendar.body_preview], recurrence as [EventsForCalendar.recurrence], categories as [EventsForCalendar.categories], hasAttachments as [EventsForCalendar.hasAttachments], webLink as [EventsForCalendar.webLink], body_contentType as [EventsForCalendar.body_contentType], body_content as [EventsForCalendar.body_content], ConvertToLocalTime([start_dateTime], 'CET', false()) as Local_start_dateTime, Date(Floor(ConvertToLocalTime([start_dateTime], 'CET', false())), 'YYYY-MM-DD') AS Startdatum, If(Floor(ConvertToLocalTime([start_dateTime], 'CET', false())) < Today(), 1, 0) AS IsBeforeCutoff, Time(ConvertToLocalTime([start_dateTime], 'CET', false()), 'hh:mm') AS Startzeit, ConvertToLocalTime([end_dateTime], 'CET', false()) as Local_end_dateTime, Date(Floor(ConvertToLocalTime([end_dateTime], 'CET', false())), 'YYYY-MM-DD') AS Enddatum, Time(ConvertToLocalTime([end_dateTime], 'CET', false()), 'hh:mm') AS Endzeit, WeekDay(ConvertToLocalTime([start_dateTime], 'CET', false())) AS Debug_WeekDay2, If([end_dateTime] >= Today(), Dual('Anstehende Termine', 1), Dual('Vergangene Termine', 0)) AS Termine, // Flag für anstehende Termine Trim(SubField(subject, '/', 1)) AS Kunde, Trim(SubField(subject, '/', 2)) AS Ort, Trim(SubField(subject, '/', 3)) AS Kategorie, Trim(SubField(subject, '/', 4)) AS Produktgruppe, // Hier wird das '#' entfernt Trim(Replace(SubField(subject, '/', 5), '#', '')) AS Kundennummer, // Gleiches auch mit _Key_Kunde da ich kundennummer nicht zugreifen kann, qlik verarbeitet alles in dem moment '1_' & Trim(SubField(subject, '/', 4)) & '_' & Trim(Replace(SubField(subject, '/', 5), '#', '')) AS _Key_Kunde, Trim(SubField(subject, '/', 6)) AS Terminart, Trim(SubField(subject, '/', 7)) AS Kollektionsquartal, Trim(Replace(SubField(subject, '/', 5), '#', '')) & '_' & Trim(SubField(subject, '/', 4)) AS %Key_Field, ApplyMap('KategorieMapping', Lower(Trim( Trim(SubField(subject, '/', 3)))), Upper(Trim( Trim(SubField(subject, '/', 3))))) as KategorieStandard WHERE WildMatch(Trim(SubField(subject, '/', 6)), 'S1', 'RT', 'OT') ; // nach Terminart wenn Struktur vielleicht geändert wird dann auf 6 setzen SELECT subject, start_dateTime, start_timeZone, end_dateTime, end_timeZone, eventId, attendees, location_displayName, importance, body_preview, recurrence, categories, hasAttachments, webLink, body_contentType, body_content FROM EventsForCalendar WITH PROPERTIES ( calendarID='AQMkADU2ZDljNDViLTJhYjAtNGJkOC04NDU5LTk1OGQzNzY4MjBkYQBGAAADA23JPdsBQEAAsbIr7L7t_ogHAKyLFHDtxs1Ctg-VodHUFiEAAAIBBgAAAMR6ZdMNzCtCueM7xFilhgcAAAIkUAAAAA==', maxResults='' ); LIB CONNECT TO 'Developer Space:Google_BigQuery_brax-dwh-377910'; [int_ausap]: LOAD Datum_Anfangstermin, Datum_Auftragseingang, Datum_Storno, Saison, Trim(Storno) as Storno, Anzahl_Bestellt, Anzahl_Fakturiert, Kundennummer as Kundennummer, Produktgruppe as Produktgruppe, Kundennummer & '_' & Produktgruppe as %Key_Field ; // Storno Feld ergänzen, Neue Mapping SQL SELECT Datum_Anfangstermin, Datum_Auftragseingang, Datum_Storno, Saison, Storno, SUM(Anzahl_Bestellt) AS Anzahl_Bestellt, SUM(Anzahl_Fakturiert) AS Anzahl_Fakturiert, Produktgruppe, Kundennummer FROM brax-dwh-377910.PROD.int_ausap WHERE CAST(SUBSTR(Saison, 3) AS INT64) >= (EXTRACT(YEAR FROM CURRENT_DATE()) - 2000 - 1) GROUP BY _Key_Kunde, Datum_Anfangstermin, Datum_Auftragseingang, Datum_Storno, Saison, Storno, Kundennummer, Produktgruppe ; [EventsForCalendar]: Load *, If (Produktgruppe = 'alle', ApplyMap('PgMapping', Upper(Trim(KategorieStandard)), (Trim(Produktgruppe))) , Produktgruppe) as Produktgruppe_alle Resident EventsForCalendar_temp; LinkTable: Load Distinct [%Key_Field], SubField(%Key_Field, '_', 1) AS Kundennummer, // Extrahiert Kundennummer SubField(%Key_Field, '_', 2) AS Original_Produktgruppe, // Nimmt den ursprünglichen Produktgruppenwert SubField(SubField(%Key_Field, '_', 2), ' ') AS Produktgruppe // Trennt Mehrfachwerte (z.B. "91 92") Resident EventsForCalendar_temp; //1 Concatenate(LinkTable) Load Distinct [%Key_Field], SubField(%Key_Field, '_', 1) AS Kundennummer, // Extrahiert Kundennummer SubField(%Key_Field, '_', 2) AS Original_Produktgruppe, // Nimmt den ursprünglichen Produktgruppenwert SubField(SubField(%Key_Field, '_', 2), ' ') AS Produktgruppe // Trennt Mehrfachwerte (z.B. "91 92") // Trennt Mehrfachwerte, Resident int_ausap; // Falls [EventsForCalendar] diese Felder enthält, kannst du sie entfernen: Drop Fields %Key_Field From [EventsForCalendar]; Drop Fields %Key_Field From [int_ausap]; Drop Fields Kundennummer, Produktgruppe From [EventsForCalendar]; Drop Fields Kundennummer, Produktgruppe From [int_ausap]; Drop Table EventsForCalendar_temp;

I am working on a data model in Qlik Sense where I have two fact tables (EventsForCalendar and int_ausap) and want to connect them through a Link Table.

🔎 Goal:

Create a Link Table using %Key_Field (which is a combination of Kundennummer and Produktgruppe).
Ensure that if a Produktgruppe has multiple values, they are split into separate rows.
Avoid synthetic keys.

🚨 Issue:

If I rename Kundennummer and Produktgruppe in the fact tables, I get synthetic keys.
If I drop them from the fact tables, the tables are not properly connected via the Link Table.

Re: Fact Tables Not Connected Properly / Synthetic Keys in Link Table

SRA — Tue, 11 Feb 2025 14:53:54 GMT

Hi,

It seems the 2 fact tables have the same key. I would :
- keep a key in each fact table with a different name
- create the link table with the 2 keys + Kundennummer and Producktgruppe.
- remove form fact tables the fields Kundennummer and Producktgruppe.

Model could look like :

It could help to add a %FactType in the link table (useful when you will create your measures in the app)

I hope it will help.
Regards,

topic Re: Fact Tables Not Connected Properly / Synthetic Keys in Link Table in App Development

Fact Tables Not Connected Properly / Synthetic Keys in Link Table

🔎 Goal:

🚨 Issue:

Re: Fact Tables Not Connected Properly / Synthetic Keys in Link Table