Creating a long table with individual days from a day range

Wed, 03 Oct 2012 09:25:12 GMT

Hi all,

My experience with loads is limited to joins and renaming columns etc, so I need a little help!

I have a table that is arranged like this:

ID's	Day Range	Value
ID1	-6*1	20
ID1	1*7	25
ID1	7*14	23
ID2	-10*1	34
ID2	1*4	36
ID2	5*10	35
ID3	-10*1	33
ID3	1*7	32
ID3	8*14	30
ID3	15*21	35

What I want to do is create a table like this (showing only ID1):

ID	Day	Value
ID1	-6	20
ID1	-5	20
ID1	-4	20
ID1	-3	20
ID1	-2	20
ID1	-1	20
ID1	1	25
ID1	2	25
ID1	3	25
ID1	4	25
ID1	5	25
ID1	6	25
ID1	7	23
ID1	8	23
ID1	9	23
ID1	10	23
ID1	11	23
ID1	12	23
ID1	13	23
ID1	14	23

Any ideas? Thanks

Mark

Re: Creating a long table with individual days from a day range

flipside — Wed, 03 Oct 2012 12:01:09 GMT

Hi Mark,

You could try this approach which basically finds the extremities of the Day Range and loops through to compare against the data.

//Test data

Data:
load

subfield([Day Range],'*',1) as d_start,
subfield([Day Range],'*',-1) as d_end
inline [
ID, Day Range, Value
ID1, -6*1, 20
ID1, 1*7, 25
ID1, 7*14, 23
ID2, -10*1, 34
ID2, 1*4, 36
ID2, 5*10, 35
ID3, -10*1, 33
ID3, 1*7, 32
ID3, 8*14, 30
ID3, 15*21, 35];

//identify range extremities

Range1:
Load

min(d_start) as r_start,
max(d_end) as r_end
resident Data;

//loop through Range1
for i= peek('r_start') to peek('r_end')

NewData:

LOAD ID as n_ID, '$(i)' as n_Day, Value as n_Value

Resident Data

where d_start <= $(i) and d_end >= $(i);

next i;

I'm not sure what logic you are using when there are 2 possible values on a single day (ID1 has -6*1 and 1*7), so you may need to add code to allow for this.

flipside

ID	Day	Value
ID1	-6	20
ID1	-5	20
ID1	-4	20
ID1	-3	20
ID1	-2	20
ID1	-1	20
ID1	1	25
ID1	2	25
ID1	3	25
ID1	4	25
ID1	5	25
ID1	6	25
ID1	7	23
ID1	8	23
ID1	9	23
ID1	10	23
ID1	11	23
ID1	12	23
ID1	13	23
ID1	14	23

ID	Day	Value
ID1	-6	20
ID1	-5	20
ID1	-4	20
ID1	-3	20
ID1	-2	20
ID1	-1	20
ID1	1	25
ID1	2	25
ID1	3	25
ID1	4	25
ID1	5	25
ID1	6	25
ID1	7	23
ID1	8	23
ID1	9	23
ID1	10	23
ID1	11	23
ID1	12	23
ID1	13	23
ID1	14	23

topic Re: Creating a long table with individual days from a day range in QlikView

Creating a long table with individual days from a day range

Re: Creating a long table with individual days from a day range

ID	Day	Value
ID1	-6	20
ID1	-5	20
ID1	-4	20
ID1	-3	20
ID1	-2	20
ID1	-1	20
ID1	1	25
ID1	2	25
ID1	3	25
ID1	4	25
ID1	5	25
ID1	6	25
ID1	7	23
ID1	8	23
ID1	9	23
ID1	10	23
ID1	11	23
ID1	12	23
ID1	13	23
ID1	14	23