topic Re: Very Interesting task for scripting in QlikView

Very Interesting task for scripting

rassanbekov — Tue, 29 Aug 2017 05:42:25 GMT

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

Re: Very Interesting task for scripting

vinieme12 — Tue, 29 Aug 2017 05:52:58 GMT

Would you be able to post some dummy data that best represents your scenario and the expected output?

Re: Very Interesting task for scripting

vinieme12 — Tue, 29 Aug 2017 05:54:26 GMT

How to get answers to your post?

Qlik Community Tip: Posting Successful Discussion Threads

Re: Very Interesting task for scripting

rassanbekov — Tue, 29 Aug 2017 06:01:56 GMT

Inputting data.

The output should look like

Job code	Month	Result
123	June	5 x 10 000 x 30%
456	June	6 x 10 000 x 30%
789	June	7 x 10 000 x 30%
123	July	(5 x 10 000 + 8 x 10 000) x 40%
456	July	(6 x 10 000 + 10 x 10 000) x 40%
789	July	(7 x 10 000 + 11 x 10 000) x 40%

Re: Very Interesting task for scripting

rassanbekov — Tue, 29 Aug 2017 06:02:14 GMT

posted

Re: Very Interesting task for scripting

effinty2112 — Tue, 29 Aug 2017 07:51:27 GMT

Hi Ruslan,

Data:

LOAD Month,

Employee,

[Job code],

HRS,

Rate

FROM

data.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join(Data)

LOAD Month,

[Job code],

Realization

FROM

[data 2.xlsx]

(ooxml, embedded labels, table is Sheet2);

Job code	Month	*HRSRateRealization/100*
123	June	15000
123	July	32000
456	June	18000
456	July	40000
789	June	21000
789	July	44000

Regards

Andrew

Re: Very Interesting task for scripting

rassanbekov — Tue, 29 Aug 2017 07:57:24 GMT

Thank Andrew. But the task is not about simple multiplication.

The formula to be written in a way to add up previous monthes and current month and multiply total to current realization rate.

It reminds me aggregation function. But I cannot set up correct formula.

Please consider that option.

Kind regards,

Ruslan

Re: Very Interesting task for scripting

effinty2112 — Tue, 29 Aug 2017 08:02:49 GMT

Hi Ruslan,

Sorry - did not read carefully enough. will respond

Andrew

Re: Very Interesting task for scripting

Peter_Cammaert — Tue, 29 Aug 2017 08:21:33 GMT

Your expression could be something like (this is in the UI, the script can be built using GROUP BY and a similar expression)

=Sum(HRS * Rate) * FirstSortedValue(Realization, -RealizationMonthStart)

The only thing you need to add when reading the Realizations, is to convert the month (in what year is this month anyway) into a MonthStart() date value and call it RealizationMonthStart.

Best,

Peter

Re: Very Interesting task for scripting

rassanbekov — Tue, 29 Aug 2017 08:38:53 GMT

Thank you, Peter!

I changed input "month" into date format. And loaded script as MonthStart (Month) into script editor. Then I just copied your expression but it didn't work out.

Is it possible if you share qvw file, where you test your expression?

Kind regards,

Ruslan

Re: Very Interesting task for scripting

Kushal_Chawda — Tue, 29 Aug 2017 09:49:26 GMT

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;

Re: Very Interesting task for scripting

rassanbekov — Tue, 29 Aug 2017 10:07:01 GMT

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

Re: Very Interesting task for scripting

rassanbekov — Tue, 29 Aug 2017 10:09:49 GMT

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

Re: Very Interesting task for scripting

Kushal_Chawda — Tue, 29 Aug 2017 10:16:47 GMT

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

Re: Very Interesting task for scripting

rassanbekov — Tue, 29 Aug 2017 10:19:14 GMT

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...

Re: Very Interesting task for scripting

Kushal_Chawda — Tue, 29 Aug 2017 10:29:51 GMT

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

Re: Very Interesting task for scripting

rassanbekov — Tue, 29 Aug 2017 12:32:29 GMT

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.

Re: Very Interesting task for scripting

rassanbekov — Tue, 29 Aug 2017 14:08:34 GMT

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