drag up data?

Wed, 11 Nov 2009 15:35:00 GMT

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

drag up data?

prieper — Sun, 15 Nov 2009 02:55:50 GMT

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

topic drag up data? in QlikView

drag up data?

drag up data?