Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
donschmitz
Contributor III
Contributor III

Using Master Calendar with multiple date tables

Qlik Group,

This is a Qlik Sense Free Cloud project.

I have 4 tables (imported as spreadsheets)

App_Provider: Contains WorkGroup, Provider_id

App_Billing: Contains Provider_id & Billing_date

App_Orders: Contains Provider_id & Order_date

App_Labs: Contains Provider_id & Lab_date

The Provider table is a master of all the providers which I have associated with all provider_id in the other tables(Billing, Orders, Labs)

Using Provider table as the filter will restrict all other tables to just that provider or WorkGroup of providers.

I would like to add a filter on Year, Quarter, Month (Master Calendar) so that the info could be filtered additionally by date.

I have watched the Master Calendar Video which I used Billing_date as what would populate with dates in my master calendar.

All seems to work with Billing data but how do I add the Orders and Labs to utilize the master calendar as it gets very messy attempting to use the Associations options within Qlik Sense.  When I try to associate the master calendar with Labs or Orders dates it seems to remove my associations on provider_id.

EndGame

I would like to have a master filter for Workgroup / Provider (this is the provider table)

and a master date filter which would work across all the other tables.

In watching and using the master calendar video it apprears you need to unlock the Auto-generated section to make any changes do I need to have all tables Imported/loaded first before attempting to add a master calendar?

Thanks for any help.

9 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Download the Qlik Sense example of this tutorial.

Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

Note this example build it's own calendars and does not use the autogenerated calendar.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

donschmitz
Contributor III
Contributor III
Author

I think I have it working to an extent.

The table will show up in Data model viewer along with a duplicate table for every table I use on the DataLink script part.

It apprears as though I need to unlock the Auto-generated section to add the tables to list of DataMangerTables as well so I can add links to them. Do I just ignore the other syn tables

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Can you post a screenshot of your table model?

-Rob

donschmitz
Contributor III
Contributor III
Author

Sending it as a PDF was the best I could do....Is there a way I can download and send the app

!

donschmitz
Contributor III
Contributor III
Author

Made some progress unlocking Autogenerate and removing all the [TableName} (sample- [APP_BILLING]: from the list leaving just APP_BILLING: and then removing the autogenerated (PROV_id as APP_BILLING.PROV_ID) and just as PROV_ID.

it now shows up in the Data Manager Linked correctly but errors on load data.

donschmitz
Contributor III
Contributor III
Author

OK, After messing around with Auto Gen I think I have it, Maybe

The only problem I still have is you used Order_Id, I used Prov_id (Provider).  I thought I could add the provider table to Link to the DataLink and filter by Name or WorkGroup.of the provider.

Do I need to add more of the provider info into the DataLink table (Name, Workgroup).

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Can you post your current data model?

-Rob

donschmitz
Contributor III
Contributor III
Author

Rob, Thanks for the help did learn a bunch but this will not work for my data.  I don't have a 1-1 to other tables its a 1-many.  I think I will have to associate all tables to Prov_id and create a master calendar and associate all the tables with a date to that master.  It gets messy in the data manager but found that if the field names for the tables are the same it will associate them almost automatically.

Again Thank you for the help, I would use this in other programs.

donschmitz
Contributor III
Contributor III
Author

I used Rob's suggestion to create a master table with a little modification.

In each of my tables I had provider_id, Date of Service, Prov_DT -> which is the conbo of date of Service and Provider_id.

Using Robs system creating a table called DataLink pulled the 3 fields in which will link date of service to a Master Calendar and Provider_id to master provider_id table (has name, ect..)

So setting filter on master provider will filter all tables by provider_id and master calendar will filter all results by date.