Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

bullish35
Contributor II

Link to generic calendar

I am using various date fields across the tables in a dashboard. I have a generic calendar but have not been able to link the dates and calendar without creating 'loosely coupled relationships' and/or synthetic keys. Any assistance getting this sorted out would be greatly appreciated.

For example, I want to link the activity_date in the Paid_ACP table to the master calendar; and, I'd like to link both the submit_date and bridge_date fields in the Policy table to the master calendar.

1 Solution

Accepted Solutions
MVP
MVP

Re: Link to generic calendar

Let's say you have data like this currently (and I'm totally guessing at the unique key for each table - each table should have a unique key, and that unique key is what you should use):

Paid_ACP:
pd_said_code, activity_date
PA1, Jan 10

Opportunity_Illustration:
illustration_plan_id, sent_date
OI1, Jan 15

Policy:
ss_policy_number, submit_date, application_date, bridge_date
P1, Jan 5, Feb 12, Feb 20

Meeting:
rn_interations_id, appt_date
M1, Feb 1

Claims4HowTo:
Ex_Claim Number, Ex_Issue Date, Ex_Reported Date
C1, Jan 20, Jan 15

You could probably link these to the Calendar like this:

CalendarLinkage:
CalendarDate, DateType, pd_said_code, illustration_plan_id, ss_policy_number, rn_interations_id, Ex_Claim Number
Jan 10, Paid Activity Date, PA1
Jan 15, Opportunity Illustration Sent Date, , OI1
Jan 5, Policy Submit Date, , , P1
Feb 2, Policy Application Date, , , P1
Feb 20, Policy Bridge Date, , , P1
Feb 1, Meeting Appt Date, , , , M1
Jan 20, Claim Issue Date, , , , , C1
Jan 15, Claim Reported Date, , , , C1

I believe that will give you a central linking table and no loops. Whether you still keep the separate dates on each table is up to you. I typically either choose separate dates and separate calendars, or a single date and a single calendar, but they CAN be mixed.

7 Replies
MVP
MVP

Re: Link to generic calendar

Let's say you have data like this currently (and I'm totally guessing at the unique key for each table - each table should have a unique key, and that unique key is what you should use):

Paid_ACP:
pd_said_code, activity_date
PA1, Jan 10

Opportunity_Illustration:
illustration_plan_id, sent_date
OI1, Jan 15

Policy:
ss_policy_number, submit_date, application_date, bridge_date
P1, Jan 5, Feb 12, Feb 20

Meeting:
rn_interations_id, appt_date
M1, Feb 1

Claims4HowTo:
Ex_Claim Number, Ex_Issue Date, Ex_Reported Date
C1, Jan 20, Jan 15

You could probably link these to the Calendar like this:

CalendarLinkage:
CalendarDate, DateType, pd_said_code, illustration_plan_id, ss_policy_number, rn_interations_id, Ex_Claim Number
Jan 10, Paid Activity Date, PA1
Jan 15, Opportunity Illustration Sent Date, , OI1
Jan 5, Policy Submit Date, , , P1
Feb 2, Policy Application Date, , , P1
Feb 20, Policy Bridge Date, , , P1
Feb 1, Meeting Appt Date, , , , M1
Jan 20, Claim Issue Date, , , , , C1
Jan 15, Claim Reported Date, , , , C1

I believe that will give you a central linking table and no loops. Whether you still keep the separate dates on each table is up to you. I typically either choose separate dates and separate calendars, or a single date and a single calendar, but they CAN be mixed.

Not applicable

Link to generic calendar

maybe you can use link tables...

but i'm not quite sure about it..

bullish35
Contributor II

Link to generic calendar

Ok, this is definitely making sense, John. So I need to create a new table (e.g., CalendarLinkage), pull in the table-specific date and key fields referenced, add a field for DateType, and depending on which table I pull from, assign a DateType to each record.

The primary scripting command will be join; is that right?

MVP
MVP

Link to generic calendar

Concatenation instead of joins. Something like this:

CalendarLinkage:
LOAD
pd_said_code
,'Paid Activity Date' as DateType
,activity_date as CalendarDate
RESIDENT Paid_ACP
;
CONCATENATE (CalendarLinkage)
LOAD
illustration_plan_id
,'Opportunity Illustration Sent Date' as DateType
,sent_date as CalendarDate
RESIDENT Opportunity_Illustration
;
etc.

bullish35
Contributor II

Link to generic calendar

I believe this is going to work, sir. I will verify tomorrow. Thank you!

bullish35
Contributor II

Link to generic calendar

This did it. I was able to exclude the dates from the separate tables' load sccripts and created the CalendarLinkage table by loading the appropriate fields directly from the QVDs we'd already created for each of those tables. Tidied up the table structure dramatically. I will upload my sample QVW with the new script in a day or so. Perhaps someone else will be able to benefit as well. Thanks again!

bullish35
Contributor II

Re: Link to generic calendar

The location of the original solution upload was posted prior to the community site revamp. I've uploaded it again. Once it has been through the approval (by Moderator) process, I'll add the new link. In the meantime, let's see if I can upload it directly to this post.