topic Apply date to all tables in QlikView

Apply date to all tables

darren_dixon — Sat, 20 Jul 2013 10:48:58 GMT

Hi,

The Main Tables all join by ResourceID and the Master Calendars link to the Main Tables.

At the moment, I have to select Appointment.Year, Unavailability.Year and Staff.Details.Year.

I want to be able select a date which is applied to all tables.

Main Tables

[Appointments]: [Unavailability]: [Staff Details]:

ResourceID, ResourceID, ResourceID,

Appointments.Date, UnavailabilityDate, StaffDetails.Date,

Master Calendars

[Appointments-MC]: [Unavailability-MC]: [Staff Details-MC]:

Appointments.Date, UnavailabilityDate, StaffDetails.Date,

Appointments.Day, Unavailability.Day, StaffDetails.Day,

Appointments.Month, Unavailability.Month, StaffDetails.Month,

Appointments.Year Unavailability.Year, StaffDetails.Year,

Thanks,

Darren

Sat, 20 Jul 2013 13:25:53 GMT

Hello Darren,

You can concatenate all your main tables together with a DataType field that will specify the source of each set of data:

Data:

Load 'Appointments' as DataType,

ResourceID,

Appointments.Date as PeriodDate

from Appointments;

concatenate(Data)

Load 'Unavailability' as DataType,

ResourceID,

Unavailability.Date as PeriodDate

from Unavailability;

concatenate(Data)

Load 'StaffDetails' as DataType,

ResourceID,

StaffDetails.Date as PeriodDate

from StaffDetails;

And you'll have one master calendar pointing to PeriodDate.

Then in your expressions just specify the DataType in the set analysis part like that:

count({$<DataType = {'Appointments'}>} field)

Hope this helps

Gysbert_Wassenaar — Sat, 20 Jul 2013 13:55:35 GMT