Link Table help

[cbushey1]

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?

[petter]

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