Split table rows into multiple rows

[stuartfrew]

Hi everyone

I have the following problem which I have been unable to solve.

I have a source table with data that I need to split across rows

Below I have tried to show an example of each table. For the fields Subject, Cat, Reason, Description - the data in this field can change every time I extract it.

The second table is an example of a fixed table

The third is ideally what I would like to happen.

Hopefully you can solve it or point me in the right direciton

[Taoufiq_Zarra]

can you explain in more detail? how do you get the third table from the two table ?

[sunny_talwar]

Should your second table look more like this?

You seem to have used 0.95 for Week 4.... I might be wrong... but just wanted to clarify

[sunny_talwar · Accepted solution]

If the above is right, you can try this

Table:
LOAD * INLINE [
    Subject, Cat, Reason, Description, Number, Week
    A, North, Broken, Machine 1, 3, 1
    B, North, Broken, Machine 1, 45, 2
    C, East, Fixed, Machine 10, 6, 3
    D, West, In Progress, Machine 55, 8, 4
    E, South, Fixed, Machine 334, 92, 5
];

Left Join (Table)
LOAD * INLINE [
    Week, Month, Apportion
    1, 1, 1
    2, 1, 1
    3, 1, 1
    4, 1, 1
    5, 1, 0.95
    5, 2, 0.05
];

FinalTable:
NoConcatenate
LOAD Subject,
	 Cat,
	 Reason,
	 Description,
	 Number * Apportion as Number,
	 Week,
	 Month,
	 Apportion
Resident Table;

DROP Table Table;