Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Morning everyone. I have a problem in calculating data under this situation:
I have 2 sets of data: One set have information for every single month. And another set only have 3 to 4 months data per year (monthly data). I want to multiply data according to month. If there is any missing data in any month, I will use the any avaible data before that.
Example: Data Set A has data 12, 13, 15 for Jan, Feb, March
Data Set B has data 0.5, X , 0.17 for Jan, Feb, March
In order to calculate Feb, I will use data from Jan (0.5) for Feb.
Problem:
1. I have no idea how to do it;
2. There is no pattern in missing data. So, if I miss out 3 months data, I will use data 4 month ago.
Many thanks,
Ivan
I have only half of a solution, which works for 2 missing entries, if there are 3, you need to add one more preceding loop.
Probably one has a better idea, how to solve?
SET_A:
LOAD * INLINE [
M, Val1
a, 12
b, 13
c, 15
d, 11
e, 10
f, 8
g, 7
h, 14
i, 15
j, 12];
SET_B:
JOIN LOAD * INLINE [
M, Val2
a, 0.5
b, 0.3
c, x
d, 0.4
e, x
f, x
g, 0.5
h, 0.6
i, 0.4
j, 0.5];
SET:
NOCONCATENATE
LOAD
M,
Val1,
IF(Val2 = 'x', PREVIOUS(Val2), Val2) AS Val2;
LOAD
M,
Val1,
IF(Val2 = 'x', PREVIOUS(Val2), Val2) AS Val2
RESIDENT
SET_A
ORDER BY
M ASC;
DROP TABLE SET_A;
Peter