Link question

[#1]

Hello there hope you can help. I'm quite new to this so please bear with me.

I'm analysing a database which contains a central table of faults. Each of these faults is tagged with a Location ID and an Organisation ID realting to where the faults was originally reported. I have successfully managhed to link the faults to the corresponding Organsation and Location tables which contain the names of the Organisationa nd Locations.

My problem begins when...

In the database you can also add additional affected Orgasniations and Locations, these are linked to the original fault by creating an entry in a seperate table containing the Location or Organsiation ID along with the Fault ID. So that you can have many locations / organisations impacted by 1 fault.

I have tried to bring this info into my report, but Qlikview doesnt seem to be seeing the 'many' relationship, when i click on a particular fault the only locations and organisations it identifies are the original ones from the central fault table.

Can anybody advise how i can get the report to recognise both types of relationship. Or if this is not possible do i need to create a seperate report for the second type of relationship, is this possible in the same application? I hope this makes sense to someone! [:)]

Thanks in advance

[johnw]

If I understood what you're doing, I probably wouldn't put ANY location or organization on the fault table. I'd move the reporting location and organization to your linkage table, and add a type to keep track of what's what. For instance:

Fault Organization Links:
Fault, Type, Organization
00001, Reported By, 10001
00001, Affects, 10005
00001, Affects, 10007

Put the Type field as a selection, and the user can decide if they only want to see the reporting organizations, only the affected organizations, or both. You can also override this on particular tables if desired with set analysis, if you, for instance, want a specific chart to ALWAYS be just by the reporting organizations.

[#3]

Thanks for your advice John. What you say makes perfect sense, the database is already set up to record the two methods of attaching an org differently, but am i right in thinking that during the load of the data into qlikview i can strip these two methods from their locations and put them into one Qlikview table... it seems like that is the way forward. Please correct me if i'm wrong! I will give this s whirl tonight.

Thanks again

Matt

[johnw · Accepted solution]

It sounds like you at least understood my suggestion, yes. Whether it's the right approach of course remains to be seen when you actually try it out! But yes, where your source data has two ways of recording links to locations and organizations, I'd try converting that to a single method in QlikView. I'd probably go ahead and load the reporting organization and location when loading your fault table for efficiency. Then, once everything else is loaded in, concatenate that data onto your link tables, and then drop the fields from your fault table. Something like this:

CONCATENATE ([Fault Organization Links])
LOAD
"Fault"
,'Reported By' as "Type"
,"Reporting Organization" as "Organization"
RESIDENT [Faults]
;
DROP FIELD "Reporting Organization";

[#5]

Thanks again for this, works a treat.