Populate rows based on Quantity

[#1]

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

[christophebrault]

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;

next;

drop table TABLE;

[jagan · Accepted solution]

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.

[#4]

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

[#5]

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

[jagan]

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.

[#7]

Thank you very much jagan.

~skip