topic Re: Linking Tables in QlikView

Linking Tables

rupshall10 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys

I cannot get my head around this:

I have 3 tables:

SiteGrouping

NutritionAudit

TissueViabilityAudit

They all have a field, SiteName (Currently called something unique in each table to prevent unwanted links)

The SiteGrouping table contains various criteria for filtering my sites.

I would like to be able to apply this sorting/grouping criteria to both the NutritionAudit and TissueViabilityAudit without linking anything else ensuring all 3 tables remain separate.

This is my first time loading and linking tables

Thanks

Re: Linking Tables

avkeep01 — Mon, 27 Nov 2017 14:38:40 GMT

Hi Richard,

Could you upload a sample data set?

Re: Linking Tables

Ben_P — Mon, 27 Nov 2017 15:39:35 GMT

A quick suggestion without seeing the data - it sounds like you should be able to do this by allowing the Site Name links to form between the tables, and then just applying various restrictions in your formula, probably using set analysis.

Re: Linking Tables

eduardo_dimperio — Mon, 27 Nov 2017 21:19:27 GMT

Maybe you could use Inner Keep to do this, but withou code or a Input/ Output is a little dificult

Understanding Join, Keep and Concatenate

Re: Linking Tables

Anil_Babu_Samineni — Tue, 28 Nov 2017 02:15:15 GMT

May be NoConcatenate will help you NoConcatenate ‒ QlikView

Re: Linking Tables

rupshall10 — Tue, 05 Dec 2017 12:12:07 GMT

Sorry for the delay I've been held up with 101 things.

It's tricky to upload a data set as this is a vast Qlik doc I've inherited.

SiteID Table which contains a list of all of the sites and details about them.

i.e

SiteName	Manager	Location
Bourne	John	South
Abbey	Paul	South
Brook	John	West

Then I have my 2 audits along the lines of the below

SiteName	Resident	Weight
Bourne	Rich	South
Bourne	Dave	South
Brook	Paul	West

I want 2 Load both audits as separate tables. These tables shouldn't have a SiteName field as I want to use the SiteName field from the SiteGroupings table.

Does this make sense?

Re: Linking Tables

avkeep01 — Tue, 05 Dec 2017 12:35:10 GMT

Hi Richard,

If I understand it correct you want to select SiteName in the SiteID Table, and the other tables should react to that? For example selecting Bourne should show Rich and Dave as Resident and South as Weight. Right?

If so the use the following script:

LOAD

SiteName AS %Site_key,

SiteName,

Manager,

Location

FROM [SiteID Table];

LOAD

SiteName AS %Site_key,

Resident,

Weight

FROM [OneOfYourAuditTables];

When selecting a SiteName the Qlik Engine will folow the line through the audit table and show you de linked residents and weights.

Re: Linking Tables

rupshall10 — Tue, 05 Dec 2017 12:45:25 GMT

Hi that's the correct understanding - unfortunately I had tried that code - or very similar and have just input it again - however when I Load SiteName AS %Site_key in both of my audit tables i get the following error message RE: a loop which should be avoided

Any way this can be improved to prevent the warning - which I deduce is bad practice?

Re: Linking Tables

avkeep01 — Tue, 05 Dec 2017 13:06:56 GMT

Hi Richard,

Try

QUALIFY *;

UNQUALIFY '%*_key';

at the beginning of the script.

In the 2 tables you've given a loop structure isn't possible. Therefore more information is necessary. Can you also add the other tables with their fields?

A loop is created when the tables link to each other for example:

Table1

SiteName

Date

Table2

Sitename

Audit

Table 3

Audit

Date

In this case Table 1 is linked to Table 2, Table 2 to Table 3 And table 3 to Table 1. To avoid this, break the loop by chaning a link between 2 tables: like date in Table 1 and Table 3.