Linking Tables

[rupshall10]

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

.

[avkeep01]

Hi Richard,

Could you upload a sample data set?

[ben_pugh]

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.

[eduardo_dimperio]

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

[Anil_Babu_Samineni]

May be NoConcatenate will help you NoConcatenate ‒ QlikView

[rupshall10]

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?

[avkeep01]

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.

[rupshall10]

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?

[avkeep01]

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.