dynamic changeindicator

[beck_bakytbek]

Hi Folks,

i have a got question, my data do look like:

year, project, value

2021, A, 50

2021, A, 70

2021, B, 30

2021, B, 20

my expected output is:

year, project, value ,    indicator,     value in %

2021,      A,        50,           no,                  0

2021,      A,        70,            increase,     40% 

2021,      B,        30,           no                   0

2021,      B,        20,            reduction    33%

to resolve this issue, i am using the previous () function but i am getting wrong results.

Does anybody have any idea how to rectify this issue?

Thanks a lot

Bek

[abhijitnalekar]

Hi @beck_bakytbek ,

Do you have any other field which indicates the time when value changes?

eg.  for project A we have 2 values but do we have any identifier which value comes first?

[justISO]

If you only need to get result from load, I would try this:

test:
LOAD * Inline
[
year, project, value
2021, A, 50
2021, A, 70
2021, B, 30
2021, B, 20
];

testNEW:
LOAD
year,
project,
value,
IF (project=Previous(project), IF (value<Previous(value), 'reduced', 'increase'), 'no') as indicator,
IF (project=Previous(project), IF (value>Previous(value), value/Previous(value)-1, 1-value/Previous(value)), 0) as [value in perc]
RESIDENT test;

drop table test;

But problem can appear when you will try to represent this data as result as grouping can be lost so I would suggest to use something like rowno() not to lose order of your rows.

[beck_bakytbek]

Hi Abhijit, thanks a lot for your reply, yes,  you are absolutely rigt

year, , Month, project, value

2021,  1, A, 50

2021, 2, A, 70

2021, 5, B, 30

2021, 6, B, 20

[abhijitnalekar · Accepted solution]

Hi @beck_bakytbek ,

You can use the script as suggested by @justISO.  I have made some changes to the script 

test:
LOAD * Inline
[
year, project, value,Month
2021, A, 50,1
2021, A, 70,2
2021, B, 30,5
2021, B, 20,6
];

testNEW:
LOAD
year,
project,
value,
Month,
IF (project=Previous(project), IF (value<Previous(value), 'reduced', 'increase'), 'no') as indicator,
num(IF (project=Previous(project), IF (value>Previous(value), value/Previous(value)-1, 1-value/Previous(value)), 0),'##.##%') as [value in perc]
RESIDENT test
order by project,year,Month;

drop table test;

[beck_bakytbek]

@abhijitnalekar 

thanks a lot your help and your time

[beck_bakytbek]

@justISO 

thanks a lot your help and your time