topic Re: Running calculations on associated fields for each table in QlikView

Running calculations on associated fields for each table

Tue, 03 Dec 2013 15:17:27 GMT

I have created two tables - one with a list of UID logins to a system, and one with a list of UID registrations. I've managed to link them together by unifying the headings, so I can browse through both simultaneously.

What I was assuming Qlikview would do was to filter both tables simultaneously according to my selections (it seems to do this) and then show the count of the records that match that filter in each table (it doesn't appear to do this). I have a chart up for $table but the figures remain the same for each table.

The tutorial I have looked at mentions this, and suggests reloading the field under a different name. That won't work here though - if I filter to only one department, I need to see the number of logins for that department (table1) and the number of registrations for that department (table2).

I'm sure the functionality must be there, I just can't get my head around where it is.

Re: Running calculations on associated fields for each table

Nicole-Smith — Tue, 03 Dec 2013 15:21:06 GMT

You can get the count of the records in two text boxes by using:

=count(logins)

and

=count(registrations)

Re: Running calculations on associated fields for each table

Tue, 03 Dec 2013 15:38:21 GMT

Thanks, I've just tried that - but as things stand, that just produces zero and the edit expression box doesn't appear to recognise the names of the tables - only the fields that I've input.

Re: Running calculations on associated fields for each table

Nicole-Smith — Tue, 03 Dec 2013 15:49:50 GMT

Can you post a .qvw?

Re: Running calculations on associated fields for each table

angelaecheverri — Tue, 03 Dec 2013 18:50:29 GMT

Nicole let me aks you something..

are you sure both dimentions have the same data?

If not... you need to make a LinkTable

Link_Table:

Load Distinct

UID_Logins as UID

From xxxx

Load Distinct

UID_Registrations as UID

From xxxx

and then you can select you UID_Logins or UID_Registrations for your new field UID

this new field will countain all your registers UID even if that UID dont share both tables...

Re: Re: Running calculations on associated fields for each table

Wed, 04 Dec 2013 11:52:55 GMT

Hi Nicole

Thanks for your time. I have attached some sample data with a similar structure. I can get a distinct count that reflects the total number of unique UIDs that are registered (because all the UIDs are listed in the second table loaded), but not one that reflects the number of unique UIDs that have logged in (i.e. the distinct count of UIDs in the first table loaded).

I appreciate that I could load the tables with unique, non-linked headings and then I could use each field independently, but that won't work when I need to filter. So if I just wanted the percentage (=logins/registrations) from central, I would need the count of unique UID logins for central, and the count of UID registrations for central - when I didn't have linked headings, I could only browse one at a time.

I hope that makes sense!

Thanks

Re: Re: Running calculations on associated fields for each table

Wed, 04 Dec 2013 11:53:51 GMT

Hi Angela

I have tried the syntax you suggest but Link_Table doesn't appear to be a recognised command in the script editor?

Re: Re: Re: Running calculations on associated fields for each table

Nicole-Smith — Wed, 04 Dec 2013 13:36:35 GMT

See the attached. If this isn't what you're looking for, I don't understand the problem...

Re: Running calculations on associated fields for each table

angelaecheverri — Wed, 04 Dec 2013 16:37:34 GMT

Try something like this:

I coundnt try it because i dont have DataSample.xls file.

SampleData:

LOAD AutoNumber(UID&'-'&Office&'-'&Name&'-'&Specialisation) as Key.SameData,

UID as UID_SampleData,

LoginDate as LoginDate_SampleData,

Office as Office_SampleData,

Name as Name_SampleData,

Specialisation as Specialisation_SampleData

FROM

[SampleData.xls]

(biff, embedded labels);

Registrations:

LOAD AutoNumber(UID&'-'&Office&'-'&Name&'-'&Specialisation) as Key.Registration,

UID as UID_Registrations,

Office as Office_Registrations,

Name as Name_Registrations,

Specialisation as Specialisation_Registrations

FROM

[SampleData.xls]

(biff, embedded labels, table is Registrations$);

LinkTable:

Load Distinct

AutoNumber(UID_SampleData&'-'&Office_SampleData&'-'&Name_SampleData&'-'&Specialisation_SampleData) as Key.SameData,

AutoNumber(UID_SampleData&'-'&Office_SampleData&'-'&Name_SampleData&'-'&Specialisation_SampleData) as Key.LinkTable,

UID_SampleData as UID,

Office_SampleData as Office,

Name_SampleData as Name,

Specialisation_SampleData as Specialisation

Resident

SampleData;

Load Distinct

AutoNumber(UID_Registrations&'-'&Office_Registrations&'-'&Name_Registrations&'-'&Specialisation_Registrations) as Key.Registration,

AutoNumber(UID_Registrations&'-'&Office_Registrations&'-'&Name_Registrations&'-'&Specialisation_Registrations) as Key.LinkTable,

UID_Registrations as UID,

Office_Registrations as Office,

Name_Registrations as Name,

Specialisation_Registrations as Specialisation

Resident

Registrations;