DI - Splitting data

[michael_jennings]

Dear All,
I have to split the quantity from a Source Table to a Result Table as describe below :
 
SOURCE TABLE
ID--------------CITY------------QTY
1----------------New York---------5
2----------------LONDON----------2
3----------------SIDNEY------------3
 
And I have to achieve the target below reported :
 
RESULT TABLE
ID--------------CITY------------QTY
1----------------New York---------1
2----------------New York---------1
3----------------New York---------1
4----------------New York---------1
5----------------New York---------1
6----------------LONDON---------1
7----------------LONDON---------1
8----------------SIDNEY-----------1
9----------------SIDNEY-----------1
10---------------SIDNEY----------1
 
 
 
How I can do this using Data Integrator ?
 
Best Regards,
MJ

[Former Member]

Hello

This is very easy assuming the QTY column has a small number of options.

Create a new table table (or use row generation transform) to prepare a dataset with 1 column - QTY.  In this dataset add the number of rows equal to QTY value.  Then join this new dataset to your original dataset on the QTY column.

For example

QTY

1

2

2

3

3

3

The are other solutions if QTY has a large number of possible values.

Michael

[michael_jennings]

Dear Michael,

        could you give me an example... Unfortunately I didn't get you.

Best Regards,

MJ

[Former Member]

Which part don't you understand?

• Create a new table/dataset
• populate dataset
• join

[michael_jennings]

Dear Michael,

          I found the very similar solution here : Create Multiple rows based on column value

Thank you and Best Regards,