Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

drag up data?

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

1 Reply
prieper
Master II
Master II

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