Develop the script

rassanbekov — Tue, 29 Aug 2017 16:47:45 GMT

Hello!

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);

topic Develop the script in QlikView

Develop the script