topic Re: Populate rows based on Quantity in QlikView

Populate rows based on Quantity

Mon, 18 Jun 2012 08:40:43 GMT

Hi,

See below data:

Doc No	Item No.	Quantity
A001	J001	1
A002	J001	3
A003	J002	4

Desired Output:
A001	J001	1
A002	J001	1
A002	J001	1
A002	J001	1
A003	J002	1
A003	J002	1
A003	J002	1
A003	J002	1

The logic is if the quantity is more than 1 it I should be able to populate rows for it.

Therefore, from the original rows, after my loop i should have 8 rows which is equal to the total Quantity.

All other fields of row whould just be repeated.

Thanks.

~skip

Re: Populate rows based on Quantity

christophebrault — Mon, 18 Jun 2012 10:02:25 GMT

Hi,

I have tested this script and works for me :

Change field name and table name and it should work for you.

TABLE:

LOAD [Doc No] as DocNo,

[Item No.] as Item,

Quantity

FROM

D:\DataUsr\chbrault\Bureau\TB01_20120618_112609.xls

(biff, embedded labels, table is Sheet1$);

for each Doc in FieldValueList('DocNo')

for i = 1 to peek('Quantity',FieldIndex('DocNo','$(Doc)')-1,'TABLE')

load '$(Doc)' as Doc,

peek('Item',FieldIndex('DocNo','$(Doc)')-1,'TABLE')as item,

'1' as Quantity

AutoGenerate 1;

next;

drop table TABLE;

Re: Populate rows based on Quantity

jagan — Mon, 18 Jun 2012 11:13:33 GMT

Hi,

Try like this

Data:

LOAD

1 AS Qty

WHILE IterNo() <= ActualQty;

LOAD * INLINE [

DocNo, Item, ActualQty

1, a, 1

2, b, 4

3, c, 2

4, d, 3

5, e, 1

];

Hope this helps you.

Regards,

Jagan.

Re: Populate rows based on Quantity

Tue, 19 Jun 2012 01:02:17 GMT

Hi Jagan,

The script is very simple and provide my desired output.

But I can't seem to understand how the script works.

Thanks.

~skip

Re: Populate rows based on Quantity

Tue, 19 Jun 2012 01:02:41 GMT

Hi Jagan,

The script is very simple and provide my desired output.

But I can't seem to understand how the script works.

Thanks.

~skip

Re: Populate rows based on Quantity

jagan — Tue, 19 Jun 2012 06:37:43 GMT

Hi,

While iterates each record for specified number of times.

For example

DocNo, Item, ActualQty

1, a, 1

2, b, 4

For record 1 (DocNo=1), ActualQty is 1 so this record iterates 1 time.

For record 2 (DocNo=2), ActualQty is 4 so this record iterates 4 time.

IterNo() - will get you the iteration number of each record. For record 2, it is 1, 2, 3, 4.

Hope this explanation helps you in understanding the concept.

Regards,

Jagan.

Re: Populate rows based on Quantity

Thu, 21 Jun 2012 07:03:30 GMT

Thank you very much jagan.

~skip