Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

Develop the script

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

0 Replies