topic Generate table with several ranges in QlikView

Generate table with several ranges

fred_s — Fri, 28 May 2010 16:02:32 GMT

I've got a table like this:

pstart	pend	pdesc
1	3	car
10	12	van
15	16	truck

This table differs per customer.

Anyone suggestions how to rebuild this table like;

1 car
2 car
3 car
10 van
11 van
12 van
15 truck
16 truck

Thanks in advance.

Generate table with several ranges

Fri, 28 May 2010 16:21:35 GMT

hi fred,

pls check the attached application

it shud work

thanks

Generate table with several ranges

boorgura — Fri, 28 May 2010 16:21:46 GMT

Get the count of pdesc.

Then use 2 FOR loops to loop through each pdesc and assign all the numbers between pstart and pend to it.

In the inner loop - use concatenate LOAD.

Let me know if it works.

Generate table with several ranges

fred_s — Fri, 28 May 2010 16:50:05 GMT

Hi Tauqueer,

Thanks! Just what I had in mind.

Rocky thanks for thinking along!

Tauqueer, I hope you don't mind to share your script with others, so others can view your solution without downloading the qvw.


mt:
LOAD * INLINE [
 pstart, pend, pdesc
 1, 3, car
 10, 12, van
 15 , 16, truck
];
let x=noofrows('mt');
for i=1 to $(x)
let y=fieldvalue('pstart',$(i));
let z=fieldvalue('pend',$(i));
for j= $(y) to $(z)
load $(j) as column1,fieldvalue('pdesc',$(i)) as column2 resident mt;
next j
next i

Generate table with several ranges

johnw — Fri, 28 May 2010 21:00:49 GMT

It's simpler and I suspect faster with a WHILE loop:

mt:
LOAD * INLINE [
pstart, pend, pdesc
1, 3, car
10, 12, van
15 , 16, truck
];
LEFT JOIN (mt)
LOAD *,pstart+iterno()-1 as pval
RESIDENT mt
WHILE pstart+iterno()-1 <= pend
;

Generate table with several ranges

fred_s — Sat, 29 May 2010 21:03:39 GMT

Hi John,

Sounds like you know the answer to just any question.
Your solution is even better.

Thanks, you're a great help to this Community.

Fred