Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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?
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.
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
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;
thanks a lot your help and your time
thanks a lot your help and your time