topic Loop all table rows in QlikView

Loop all table rows

Wed, 16 Dec 2009 18:41:15 GMT

Hello.

I have a table like this:

material	price	qty
66929	€ 1,20	3
66929	€ 1,25	5
66929	€ 1,70	2
15894	€ 5,10	10
66929	€ 1,20	3

For simplicity, let say we have only one material (66929). What I need is work out this table into another one like this:

material	no.	price
66929	1	€ 1,20
66929	2	€ 1,20
66929	3	€ 1,20
66929	4	€ 1,25
66929	5	€ 1,25
66929	6	€ 1,25
66929	7	€ 1,25
66929	8	€ 1,25
66929	9	€ 1,70
66929	10	€ 1,70
66929	11	€ 1,20
66929	12	€ 1,20
66929	13	€ 1,20

As you can see, in this last table I have listed all the materials one by one, according to their quantity. I have therefore three items at € 1,20, five at € 1,25 and so on.

In QlikView, I need two loops: one that scrolls each row and a nested one that will loop as many time as stated in the corresponding quantity. I'll thus create this second table including the RowNo() (called "no." in the example).
I'm able to loop all the rows, but I can't loop the single row according the the reported quantity. Any idea, please?

The code I've written till now is (assuming "Details" is the first table and "r" is the RecNo() of the first table, calculated and included for each row):

LET NrRows = NoOfRows('Details');
LET k = 1; //counter for rows
DO WHILE r<=NrRows //loop all the rows
 LET i=0;
 DO WHILE i<PEEK(qta_originale,r,'Details'); //I need to loop the selected row as many times as quantity is. What I've writtend here doesn't work since the PEEK function is not in a load statement
 LOAD RowNo() as n, PEEK(material,r,'Details') as material, PEEK(price,r,'Details') as price
 RESIDENT Details;
 i=i+1;
 LOOP
 k=k+1;
LOOP

AW:Loop all table rows

Wed, 16 Dec 2009 21:38:30 GMT

Hi,

I had an idea, but I don't know if it was good.

Try:


let z1=1;

DO WHILE z1<=20
 let z2=1;
 Do While z2<= z1
 Tab1:
 LOAD * INLINE [
 Qty
 $(z1)
 ];
 let z2=z2+1;
 LOOP
 let z1=z1+1;
LOOP


Tab1:
right join (Tab1) load * INLINE [
 MatNr, Price, Qty
 12, 1.29, 2
 12, 1.88, 8
 12, 0.45, 2
 13, 12.90, 1
];

You should calculate the first table once with more numbers and store it as a QVD. Then you can load it faster.

Have Fun

Alex:)

AW:Loop all table rows

Wed, 16 Dec 2009 22:53:08 GMT

Thank you, Alexander. I'll work on your idea. Now I need to include a counter in the table so I can have ordered rows.

Thank you for your help and time!

AW:Loop all table rows

johnw — Thu, 17 Dec 2009 07:01:23 GMT

Here's a loopless approach:

LOAD *
,recno() as no
;
LOAD
subfield(material&repeat(','&material,qty-1),',') as material
,price
INLINE [
material, price, qty
66929,1.20,3
66929,1.25,5
66929,1.70,2
15894,5.10,10
66929,1.20,3
];

AW:Loop all table rows

johnw — Thu, 17 Dec 2009 07:32:27 GMT

A completely different loopless approach:

Raw:
LOAD *
,1 as start
,qty as end
,recno() as recno
INLINE [
material,price,qty
66929,1.20,3
66929,1.25,5
66929,1.70,2
15894,5.10,10
66929,1.20,3
];
Counter:
LOAD recno() as counter
AUTOGENERATE 10
;
LEFT JOIN (Counter)
LOAD recno
RESIDENT Raw
;
LEFT JOIN (Raw)
INTERVALMATCH(counter,recno)
LOAD
start
,end
,recno
RESIDENT Raw
;
Final:
LOAD
material
,price
,recno() as no
RESIDENT Raw
ORDER BY recno
;
DROP TABLES
Raw
,Counter
;

AW:Re: AW:Loop all table rows

Thu, 17 Dec 2009 14:32:32 GMT

Hi John,

I would fall on my knees 😉

I never know what these two functions are for. Great performance ...

Happy Hollydays

Alex :-)>

AW:Loop all table rows

Thu, 17 Dec 2009 22:08:36 GMT

Thank you very much, John. Your script does exactly what I need!

Thank you again for your time and for your help.