Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I read through the existing posts and couldnt find my solution so I thought I would ask.
I am trying to create a link table as I have a data model with 10+ tables. I started by creating my own key by concatenating some fields together in each table and pushed all the "keys" from each table into the Link Table.
Here is what I have so far:
TempLink:
Load
(FOCUS_ID_NO & '_' & CodeBlueLocation & '_' & CODE_DATE) as %KeyCodeBlue
,FOCUS_ID_NO
,CodeBlueLocation as Location
,CODE_DATE as EventDate
Resident TempCodeBlue;
Concatenate(TempLink)
Load Distinct
(INCIDENTID & '_' & [Event Date] & '_' & [Location where Event Occurred]) as %KeyRLIncident
,INCIDENTID
,[Event Date] as EventDate
,[Location where Event Occurred] as Location
Resident TempIncident;
Concatenate(TempLink)
Load Distinct
(AbstractID & '_' & Account & '_' & LocationName & '_' & EventStartDate) as %KeyMediLOS
,AbstractID
,Account
,LocationName as Location
,EventStartDate as EventDate
Resident TempMediLocations;
I have realized though that I am getting a lot of dupes for EventDate and I really dont want to keep loading the same date into that field.
Any suggestions on how to make this link table work more efficiently?
Duplicates in itself is less worrying in QlikView in a single field because it internally/automatically do a de-duplication that is highly efficient.
In a concatenated keyfield it is much more problematic.
You should use the AutoNumber() function to make the key as storage-efficient as possible and make sure that the EventDate is really just a date and does not contain any time in it by for instance doing a Floor(EventDate) and also turns into an integer. This will make sure that you get the best distinct ratio for the EventDate ( unique values / all values ).