combine data driven by two calendars into one

[#1]

Attached is a QVW application that shows three charts.

The 1st is a chart that displays some expression (using one type of data) across a calendar created for that specific data.

The 2nd is a chart that displays some expression (using another type of data) across a calendar created for that specific data.

The 3rd is my so-far-failed attempt at combining the first two charts into one, across one generic calendar for both sets of data.

Any ideas?

[fernandotoledo]

You should link your sources through the Date Field with a link Table:

Table1:
Load
 Date1,
 Date1 as PK_Date
From Table1
;

Table2:
Load
 Date2,
 Date2 as PK_Date
From Table2
;

LinkTable:
Load DISTINCT
 PK_Date,
 Date1 as Date
Resident Table1
;
Load DISTINCT
 PK_Date,
 Date2 as Date
Resident Table2
;

The "Date" Field will work in all Charts

Best regards,

Fernando

[#3]

Fernando,

Thanks for your response. I believe I must be missing something because I tried various approaches given your advice and I'm getting either a "field not found" error when I try to run that script or a "there may be one of more loops" error.

Here's a simplification of my script. I have attached the work in progress QVW as it may be easier to take a look at my actual script. I've marked the tabs tabs and areas of script with the comment "Fernando" so it'll be easier to find via a string search.

ProjectHistoryFieldPivot:
LOAD
EffTime as ProjectHistoryFieldPivot.EffTime,
date(daystart(EffTime)) AS ProjectHistoryFieldPivot.MasterCalendarFK,
*
FROM GetProjectHistoryFieldListPivot.csv (ansi, txt, delimiter is ',', embedded labels, msq);
QuestionnaireResponseFieldValue:
LOAD
StartTime as QuestionnaireResponseFieldValue.StartTime,
date(daystart(StartTime)) AS QuestionnaireResponseFieldValue.MasterCalendarFK
FROM GetQuestionnaireResponseFieldValueList.csv (ansi, txt, delimiter is ',', embedded labels, msq);
LinkedCalendar:
Load DISTINCT
QuestionnaireResponseFieldValue.MasterCalendarFK,
weekend(date(daystart(StartTime))) as LinkedDate
Resident QuestionnaireResponseFieldValue;
Load DISTINCT
ProjectHistoryFieldPivot.MasterCalendarFK,
weekend(date(daystart(EffTime))) as LinkedDate
Resident ProjectHistoryFieldPivot;

Thanks again for all your help!