topic Re: Calculation on Column itself in QlikView

Calculation on Column itself

supriyabiware — Fri, 15 Dec 2017 12:54:49 GMT

Hi All,

I have a small requirement as below. Can anybody help in this?

You can suggest any script calculation or Set analysis (i prefer script calculation)

i want to reduce column APREIS depending on % Reduction - Expected result is as below...

Note - Comma is decimal point in Last column

Thanks

Supriya

Re: Calculation on Column itself

YoussefBelloum — Fri, 15 Dec 2017 13:02:01 GMT

Hi,

reduced:

LOAD Year,

%Reduction,

Apreis,

Apreis-(Apreis*%Reduction) as Reduced

attached below

Re: Calculation on Column itself

supriyabiware — Fri, 15 Dec 2017 13:10:55 GMT

Thanks youseff for your reply.

i have a slight twist here..

i need to reduced the APREIS with already reduced apreis from previous Year.

Attached is the sample .

Re: Calculation on Column itself

YoussefBelloum — Fri, 15 Dec 2017 13:16:27 GMT

How can i Know the already reduced amounts last year ? to reduce them

Re: Calculation on Column itself

supriyabiware — Fri, 15 Dec 2017 13:17:41 GMT

thats the requirement it can be easily done in excel!

Re: Calculation on Column itself

YoussefBelloum — Fri, 15 Dec 2017 13:24:02 GMT

when i say how, I'm talking with the sample data you provided.

there is no indication here.. what is the calculation rule ??

Re: Calculation on Column itself

supriyabiware — Fri, 15 Dec 2017 13:36:37 GMT

Considering Headers as row 1 i have applied below formula in column N

1st row - M2*(1-L2)

2nd row - N2*(1-L3)

3rd row - N3*(1-L4)

4th row - N4*(1-L5)

.....so on

Re: Calculation on Column itself

sunny_talwar — Fri, 15 Dec 2017 13:52:14 GMT

May be this

Table:

LOAD * INLINE [

Year, % Reduction, Apreis

2016, 0, 1231

2017, 0, 1231

2018, 0.03, 1231

2019, 0.03, 1231

2020, 0.03, 1231

2021, 0, 1231

2022, 0, 1231

2023, 0, 1231

2024, 0, 1231

2025, 0, 1231

2026, 0, 1231

2027, 0, 1231

];

TempTable:

LOAD *,

Alt(Peek('% Reduction'), 0) as [New % Reduction]

Resident Table

Order By Year Desc;

Final:

LOAD *,

[New % Reduction] * [Reduced APREIS] as Reduction;

LOAD *,

RangeSum(Alt(Peek('Reduced APREIS'), [Apreis]), -Alt(Peek('Reduction'), 0)) as [Reduced APREIS]

Resident TempTable

Order By Year;

DROP Table Table, TempTable;

Re: Calculation on Column itself

supriyabiware — Fri, 15 Dec 2017 14:22:44 GMT

PERFECT !!! Thankyou so much!

Re: Calculation on Column itself

supriyabiware — Wed, 20 Dec 2017 10:04:09 GMT

i have one more thing to do here.. i have to do this calculation for one set of IDs. so i did this

if(ID=Previous(ID), RangeSum(Alt(Peek('REDUCED_APREIS'), APREIS_1), -Alt(Peek('TEMP'),0))) as REDUCED_APREIS

But something is not working here -i am getting blank for first row..

Help appreciated thanks

Re: Calculation on Column itself

sunny_talwar — Wed, 20 Dec 2017 12:28:39 GMT

Where is the id field in the image? Can you provide how the new data look like? I will have to test it out a little to give you a solution based on changing id

Re: Calculation on Column itself

supriyabiware — Wed, 20 Dec 2017 13:01:51 GMT

Something like attached.

i want same operation only for one ID at a time. it should have reduced APREIS for one ID and start newly with next ID

(i have millions of IDs in my main Data model)

Re: Calculation on Column itself

sunny_talwar — Wed, 20 Dec 2017 13:12:36 GMT

Try something like this

Table:

LOAD * INLINE [

ID,Year, % Reduction, Apreis

1,2016, 0, 1231

1,2017, 0, 1231

1,2018, 0.03, 1231

1,2019, 0.03, 1231

1, 2020, 0.03, 1231

1 ,2021, 0, 1231

1 ,2022, 0, 1231

1 ,2023, 0, 1231

1 ,2024, 0, 1231

1 ,2025, 0, 1231

1, 2026, 0, 1231

1 ,2027, 0, 1231

2,2016, 0, 14677

2,2017, 0.05, 14677

2,2018, 0.05, 14677

2,2019, 0.05, 14677

2, 2020, 0, 14677

2 ,2021, 0, 14677

2 ,2022, 0, 14677

2 ,2023, 0, 14677

2 ,2024, 0, 14677

2 ,2025, 0, 14677

2, 2026, 0, 14677

2 ,2027, 0, 14677

3,2016, 0, 12345

3,2017, 0.01, 12345

3,2018, 0.01, 12345

3,2019, 0.01, 12345

3, 2020, 0, 12345

3 ,2021, 0, 12345

3 ,2022, 0, 12345

];

TempTable:

LOAD *,

If(ID = Previous(ID), Alt(Peek('% Reduction'), 0), 0) as [New % Reduction]

Resident Table

Order By ID, Year Desc;

Final:

LOAD *,

[New % Reduction] * [Reduced APREIS] as Reduction;

LOAD *,

If(ID = Previous(ID), RangeSum(Alt(Peek('Reduced APREIS'), [Apreis]), -Alt(Peek('Reduction'), 0)), [Apreis]) as [Reduced APREIS]

Resident TempTable

Order By ID, Year;

DROP Table Table, TempTable;

Re: Calculation on Column itself

sunny_talwar — Wed, 20 Dec 2017 13:13:21 GMT

Is it possible to have reduction % for the very first year also?

Re: Calculation on Column itself

supriyabiware — Wed, 20 Dec 2017 13:23:46 GMT

yes in few cases i do have %reduction in first year as well! .

Trying the above logic.. Thanks a lot!

Re: Calculation on Column itself

sunny_talwar — Wed, 20 Dec 2017 13:26:07 GMT

In that case I would concatenate a dummy row for all your IDs with a very small year and delete that extra row at the very end...

Re: Calculation on Column itself

sunny_talwar — Wed, 20 Dec 2017 13:29:39 GMT

Try like this

Table:

LOAD * INLINE [

ID,Year, % Reduction, Apreis

1,2016, 0, 1231

1,2017, 0, 1231

1,2018, 0.03, 1231

1,2019, 0.03, 1231

1, 2020, 0.03, 1231

1 ,2021, 0, 1231

1 ,2022, 0, 1231

1 ,2023, 0, 1231

1 ,2024, 0, 1231

1 ,2025, 0, 1231

1, 2026, 0, 1231

1 ,2027, 0, 1231

2,2016, 0.05, 14677

2,2017, 0.05, 14677

2,2018, 0.05, 14677

2,2019, 0.05, 14677

2, 2020, 0, 14677

2 ,2021, 0, 14677

2 ,2022, 0, 14677

2 ,2023, 0, 14677

2 ,2024, 0, 14677

2 ,2025, 0, 14677

2, 2026, 0, 14677

2 ,2027, 0, 14677

3,2016, 0, 12345

3,2017, 0.01, 12345

3,2018, 0.01, 12345

3,2019, 0.01, 12345

3, 2020, 0, 12345

3 ,2021, 0, 12345

3 ,2022, 0, 12345

];

Concatenate (Table)

LOAD DISTINCT ID,

1 as Year,

0 as [% Reduction],

Apreis

Resident Table;

TempTable:

LOAD *,

If(ID = Previous(ID), Alt(Peek('% Reduction'), 0), 0) as [New % Reduction]

Resident Table

Order By ID, Year Desc;

Final:

LOAD *,

[New % Reduction] * [Reduced APREIS] as Reduction;

LOAD *,

If(ID = Previous(ID), RangeSum(Alt(Peek('Reduced APREIS'), [Apreis]), -Alt(Peek('Reduction'), 0)), [Apreis]) as [Reduced APREIS]

Resident TempTable

Order By ID, Year;

FinalFinalTable:

NoConcatenate

LOAD *

Resident Final

Where Year <> 1;

DROP Table Table, TempTable, Final;

Re: Calculation on Column itself

supriyabiware — Wed, 20 Dec 2017 14:12:17 GMT

PERFECT! as usual Thanks a ton