Link Table help

cbushey1 — Wed, 12 Aug 2015 12:21:13 GMT

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?

Re: Link Table help

petter — Wed, 12 Aug 2015 12:34:26 GMT

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

topic Re: Link Table help in QlikView

Link Table help

Re: Link Table help