Need to link by Date fields

[Eaun]

Hi

Been trying to link two date fields that are at different levels of granularity

One is by Month and the other is Day I have created a ID to link the month between the month scrap allowance and the master calendar so the scarp month total can be worked out by how many working days in each month

what I would like to do is when a month is clicked I get the Daily totals for each month I just can not see how to use a link table so I can join the scrap days into the scrap month. Any help would be greatly appreciated 

[Lisa_P · Accepted solution]

Here is your script with a link table to join based on MonthYear and CostCentre

[SCRAP]:
LOAD
Date(MonthStart(ALLOWANCE_DATE),'MMM-YYYY')& '|' & COST_CENTER as YMCC_Key,
ALLOWANCE_DATE,
SCRAP_TOTAL,
COST_CENTER as SCRAP_COST_CENTER,
// %ID,
NUMBER_OF_DAYS
FROM [lib://test/SCRAP.qvd]
(qvd);

[SCRAP_REPORT]:
LOAD
Date(MonthStart(CalDate),'MMM-YYYY')& '|' & COST_CENTER as YMCC_Key,
date(date#(CalDate),'YYYY\MM\DD') as CalDate,
// %SCRAPMONTH,
COST_CENTER as REPORT_COST_CENTER,
QTY_TO_SCRAP
FROM [lib://test/SCRAP_REPORT.xls]
(biff, embedded labels, table is Data$)
where CalDate >='01042019';

YEAR_CALENDAR:

// Load Distinct
// *,
// AutoNumber(%ALLOWANCEMONTH) AS %ID;

LOAD *,

DaysInMonth - WorkingDaysInMonth AS WeekendDaysInMonth;
Load
*,
NetworkDays(%ALLOWANCEMONTH, MonthEnd(%ALLOWANCEMONTH)) AS WorkingDaysInMonth;

LOAD
REFERENCE_DATE,

num(Day(MonthEnd(REFERENCE_DATE))) AS DaysInMonth,
Day(REFERENCE_DATE) as DAY,
Month(REFERENCE_DATE) as MONTH,
Year(REFERENCE_DATE) as YEAR,
Week(REFERENCE_DATE) as WEEK,
'Q' & Ceil(Month(REFERENCE_DATE)/3) as QUARTER,
Date(MonthStart(REFERENCE_DATE),'MMM-YYYY') as MONTH_YEAR,
InYearToDate(REFERENCE_DATE,'$(vToday)',0)*-1 as CYDFlag,
InYearToDate(REFERENCE_DATE,'$(vToday)',-1)*-1 as LYDFlag,
DATE(DATE#('01'&RIGHT(0&NUM(MONTH(REFERENCE_DATE)),2)&Year(REFERENCE_DATE)),'DDMMYYYY') as %ALLOWANCEMONTH;

Load
Date(AllowanceMinDate + IterNo()) as REFERENCE_DATE
While AllowanceMinDate + IterNo() <= AllowanceMaxDate;

Load
num(min(FieldValue('ALLOWANCE_DATE',RecNo()))) as AllowanceMinDate,
num(Max(FieldValue('ALLOWANCE_DATE',RecNo()))) as AllowanceMaxDate
AutoGenerate FieldValueCount('ALLOWANCE_DATE');

LinkTable:
Load Distinct
Date(MonthStart(ALLOWANCE_DATE),'MMM-YYYY')&'|'& SCRAP_COST_CENTER as YMCC_Key,
Date(MonthStart(ALLOWANCE_DATE),'MMM-YYYY') as MONTH_YEAR,
SCRAP_COST_CENTER as COST_CENTER,
'SCRAP' as FromTable
Resident SCRAP;

Concatenate(LinkTable)
Load Distinct
Date(MonthStart(CalDate),'MMM-YYYY')&'|'& REPORT_COST_CENTER as YMCC_Key,
Date(MonthStart(CalDate),'MMM-YYYY') as MONTH_YEAR,
REPORT_COST_CENTER as COST_CENTER,
'REPORT' as FromTable
Resident SCRAP_REPORT;

[Eaun]

Thank you that worked

I can see how the link works now the only thing I'm not sure of is :

'SCRAP' as FromTable and 'REPORT' as FromTable what are they used for ?

[Lisa_P]

This is typically a part of the process when creating a link table in case we need to troubleshoot the key fields, we know where they come from.

[Eaun]

I see now it just creates a tag for each record  very useful

thank you for help