I am trying to properly write the script for loading data.
The main task is to make the calculation: Hrs x [Rate (KZT)] x Realisation for each [job code].
The issue relates to "Realisation". I need to load realisation out of the maximum available "date" per "job code". Then apply the realisation value to the above formula.
Additionally, input does not match "job code" completely with Advisory table. In respect of unmatching ones, the realization should equal to "40". For that purpose, I consider the ApplyMap function. For now I cannot appropritely use Mapping since the number of fields more than two.
So far I have the following script, I believe you can find easy way to properly fix out the formula.
Realization:
LOAD [Job code],
Realisation,
Period,
Period&'_'& [Job code] as KEY
FROM
[Data\Realizations by month.xlsx]
(ooxml, embedded labels, table is Sheet1);
INNER JOIN (Realization)
LOAD [Job code],
Max(Period) as Period
RESIDENT Realization
WHERE Realisation > 0
GROUP BY [Job code];
DROP TABLE (Realization);
Realizations:
LOAD
Realisation,
[Job code]&'_'&Period as KEY
FROM [Data\Realizations by month.xlsx] (ooxml, embedded labels, table is Sheet1);
MapRealization:
Mapping
LOAD Distinct KEY, Realisation
Resident Realizations;
Advisory:
LOAD BU,
[Client name],
[Job code],
[Eng. name],
[Work dt],
Period,
Month(Period) as Date,
QuarterStart(Period) as Quarter,
Hrs,
Tr.Reference,
Tr.Type,
Descript,
Ref.,
Entity,
[Tr type],
[Journ Type],
[Empl code],
[Empl name],
[Empl office],
[Empl dep],
[Eng CC],
EngMan,
[Job code]&'_'&Period as KEY,
ApplyMap('MapRealization', [Job code]&'_'&Period,40) as AdjRealization
FROM [Data\July'17 ch hours details_kz _emc.xls] (biff, embedded labels, table is Advisory$);
Inner Join (Advisory)
MC_Employees:
LOAD [Empl name],
Grade,
City,
Group,
Month(Period) as Date
FROM [Data\MC employee list.xlsx] (ooxml, embedded labels, table is Sheet1);
MC_Rates:
LOAD Position as Grade,
[Rate (KZT)]
FROM [Data\MC rates in KZT.xlsx] (ooxml, embedded labels, table is Sheet1);
Budget:
LOAD Month(Date) as Date, Group, Amount as BudgetAmount
FROM Data\Budget.xlsx (ooxml, embedded labels, table is Sheet1);
