Hi,
Try this expression for you i add a script for the common dates and the resident table to get the table flag fields
QuartersMap:
MAPPING LOAD
RowNo() as Month,
'Q' & Ceil (RowNo()/3) as Quarter
AUTOGENERATE (12);
//*********************************New Table Add
Temp1:
Load Distinct
[DateDispensed] as CommonDate,
[DateDispensed],
'Main Data Table' as TableFlag
Resident [Main Data];
Concatenate(Temp1)
LOAD Distinct
ActionDate as CommonDate,
ActionDate,
'MCAZ APPROVED Table' as TableFlag
Resident [Main Data];
Temp2:
LOAD
min(Date(Floor(CommonDate))) as minDate,
max(Date(Floor(CommonDate))) as maxDate
Resident Temp1;
LET varMinDate = Num(Peek('minDate', 0, 'Temp2'));
LET varMaxDate = Num(Peek('maxDate', 0, 'Temp2'));
DROP Table Temp;
TempCalendar:
LOAD DISTINCT
$(varMinDate) + IterNo()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate,
Date($(varMinDate) + IterNo() - 1) as CommonDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
//*****************************
//For get the TableFlagfield into the calendar
Left Join(TempCalendar)
LOAD
CommonDate,
TableFlag
Resident Temp1;
//***************************************
MasterCalendar:
Load *,
AutoNumber(Year & Quarter, 'QuarterID') as [QuarterID],
AutoNumber(Period, 'PeriodID') as [PeriodID];
LOAD DISTINCT
TempDate as [DateDispensed],
Year(TempDate) * 100 + Month(TempDate) as [Period],
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
YearToDate(TempDate)*-1 as CurYTDFlag,
YearToDate(TempDate, -1)*-1 as LastYTDFlag,
InYear(TempDate, MonthStart($(varMaxDate)),-1) as RC12,
Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', Month(TempDate), Null()) as Quarter,
Week(Weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate asc;
DROP Table TempCalendar;
Filtered:
LOAD
Year as Last5Year
Resident [MasterCalendar] Where Year >= Year(Today()) - 4 and Year <= Year(Today());
calTemp1:
LOAD Distinct
Year,
Week
Resident MasterCalendar
Order by Year, Week;
Left Join (MasterCalendar)
LOAD
Year,
Week,
RowNo() as weekYearSeq
Resident calTemp1
Order By Year, Week;
DROP Table calTemp1;
////--- Remove the temporary variables
LET varMinDate = Null();
LET varMaxDate = Null();
//The other table where the date dispensed is as follows:
[Main Data]:
LOAD [Script Number],
[Drug Name],
Date(Floor([Date Dispensed])) as DateDispensed,
// Directions,
[Repeat or Original],
[Doctor Name],
[Doctor Number],
QTY,
[MedAid Amount],
Shortfall,
[Med Aid] as [Payment Type],
[Supply days],
[Drug Code]
FROM
[$(vFolderSourceData)Datawarehouse_2.xls]
(biff, embedded labels, table is Datawarehouse$);
STORE [Main Data] into '$(vFolderSourceData)QVDs\
PrescriptionHistory.qvd' (qvd);
The new table i want to add is as follows:
[MCAZ APPROVED]:
LOAD [APPLICATION #]as ApplicationNumber,
[TRADE NAME],
[GENERIC NAME],
FORM,
STRENGTH,
CATEGORY,
[REGISTRATION #] as RegistrationNumber,
ActionDate,
APPLICANT,
MANUFACTURER
// MANUFACTURER1,
// MANUFACTURER2,
// manufname4,
// manufcode5,
// manufname5
FROM
[$(vFolderSourceData)tblMCAZApproved.xls]
(biff, embedded labels, table is regis_h$);
Regards
Anand
