Very Interesting task for scripting

[rassanbekov]

Hello, guys!

Very intersting task for Qlikview scripting.

I have data set for ''project code'', "hours", "rate"  and "realization" rate.

The income is recognized when hours x rate x realization.

However, the realization changes over time.

When the realization changes the income is recongized by last available realization.

The formula should aggregate the value like "hours" ((1) x "rate" (1) + "hours" (2) + "rate" (2)) x realization (2). something like this.

I include the input data. Please propose any script.

Kind regards,

Ruslan

[Kushal_Chawda]

Data:

LOAD Month,

    num(month(Date#(Month,'MMMM'))) as MonthNum,

    Employee,

    [Job code],

    HRS,

    Rate,

    HRS*Rate as HRSRate

FROM

[data (1).xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(Data)

LOAD Month,

    [Job code],

    Realization/100 as Realization

FROM

[data (1).xlsx]

(ooxml, embedded labels, table is Sheet2);

Final:

LOAD *,

    HRS_Rate*Realization as Results;

LOAD *,

    if([Job code]<>Previous([Job code]),HRSRate,rangesum(HRSRate,Peek('HRS_Rate'))) as HRS_Rate

Resident Data

Order by [Job code],MonthNum;

DROP Table Data;

DROP Fields HRSRate,HRS_Rate;

[rassanbekov]

The logic is correct.

However, when I select July, the total amount is shown for June+July, despite the July amounts should be shown only. The aggregated value should be shown as total if I selected both months.

Could you please fix it?

Kind regards,

Ruslan

[rassanbekov]

Finally it worked out with this example. Unfortunately, when I transfer to existing data model not all job codes are recalculated.

let me share to you my qvw file?

Kind regards,

Ruslan

[Kushal_Chawda]

What should be the Amount when you select July and when you select June & july?

[rassanbekov]

When I select July, the formula should use realization for July. When I select June, the formula should select the realization for June, respectively. But when no selections made or selections of both months, the formula should use the latest available realization rate...

[Kushal_Chawda]

Please provide the expected output in all the scenario. I am still confused with requirement

[rassanbekov]

The scenario 1:

Select June:

job code	Month	HRS	Rate	Realization	Result
123	June	5	10 000	30%	15 000

The same results for other job codes as well.

Select July:

Job code	Month	Hrs	Rate	Realization	Result
123	Julty	8	10 000	40%	32 000

Select both Month or Select nothing

job code	Month	Hrs	Rate	Realization	Results
123	-	13	10 000	40%	52 000

Hope it's clear.

[rassanbekov]

Please see the script maybe due to the error in scrip I am unable to apply the formula:

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