How to aggregate multiple lines into single line in Script?

[mrthomasshelby]

Hello. I have a table in an excel file which looks like:

Company	Craft	Craft Class	Craft Line	Rate Type	CCM-Vend Cd	Rate Component Code	Rate Component Descr	Amount
123 Enterprises	BM	GF	1	1-ST	<ENTER>	PT	PT-FICA	$0.27
123 Enterprises	BM	GF	1	1-ST	<ENTER>	PT	PT-FUI	$0.02
123 Enterprises	BM	GF	1	1-ST	<ENTER>	PT	PT-SUI	$0.00

Now the task is to combine lines with similar attribute in the column 'Rate Component Code'. In other words, the flattened table for the above 3 lines with the same 'Rate Component Code' will be:

Company	Craft	Craft Class	Craft Line	Rate Type	Rate Component Code	Amount
123 Enterprises	BM	GF	1	1-ST	PT	$0.29

I saw some examples of Generic loads being used to achieve this but I'm unable to proceed further. Please help me achieve this. Attaching the files for your reference. Thanks in advance! @sunny_talwar

[shiveshsingh · Accepted solution]

Hi

Try this

T:LOAD * INLINE [
Company, Craft, Craft Class, Craft Line, Rate Type, CCM-Vend Cd, Rate Component Code, Rate Component Descr, Amount
123 Enterprises, BM, GF, 1, n/a, , CCM-SC, CCM-Service Code, 0
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, SSM-VC, CCM-Vendor Code, <ENTER>
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, CCM-TC, CCM-Time Code, ST
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, BW-100, Base Wage-100%, $0.00
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, BW-90, Base Wage-100%, $0.00
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, FB, Vacation, $3.50
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, SUPV, Supervision Adder, n/a
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, BW-TB, Base Wage - Taxable, $3.50
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, PT, PT-FICA, $0.27
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, PT, PT-FUI, $0.02
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, PT, PT-SUI, $0.00
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, FB, FB-Health & Welfare, $8.57
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, FB, FB-Pension, $14.55
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, FB, FB-Annuity, $1.50
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, FB, FB-Apprenticeship, $2.40
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, FB, FB-Local 92 JATC, $3.00
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, FB, FB-National Annuity, $0.34
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, FB, FB-Most, $0.15
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, FB, FB-WSBIF, $0.00
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, FB, FB-N/A, $0.00
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, FB, FB-N/A, $0.00
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, INS, INS-Workers Comp, $0.00
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, INS, INS-Employers Liability, $0.00
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, INS, INS-General Liability, $0.00
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, INS, INS-Umbrella, $0.00
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, INS, INS-Other, $0.00
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, MISC, MISC-Small Tools, $0.00
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, MISC, MISC-Consumables, $0.00
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, MISC, MISC-PPE, $0.00
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, MISC, MISC-Other, $0.00
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, OP, OP-Overhead, $0.00
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, OP, OP-Profit, $0.00
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, BR, Billing Rate, $34.30
123 Enterprises, BM, GF, 1, 1-ST, <ENTER>, MULT, Multiplier, 0
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, SSM-VC, CCM-Vendor Code, <ENTER>
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, CCM-TC, CCM-Time Code, OT
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, BW-100, Base Wage-100%, $0.00
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, BW-90, Base Wage-100%, $0.00
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, FB, Vacation, $5.25
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, SUPV, Supervision Adder, n/a
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, BW-TB, Base Wage - Taxable, $5.25
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, PT, PT-FICA, $0.40
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, PT, PT-FUI, $0.03
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, PT, PT-SUI, $0.00
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, FB, FB-Health & Welfare, $8.57
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, FB, FB-Pension, $21.83
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, FB, FB-Annuity, $1.50
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, FB, FB-Apprenticeship, $2.40
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, FB, FB-Local 92 JATC, $4.50
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, FB, FB-National Annuity, $0.34
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, FB, FB-Most, $0.23
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, FB, FB-WSBIF, $0.00
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, FB, FB-N/A, $0.00
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, FB, FB-N/A, $0.00
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, INS, INS-Workers Comp, $0.00
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, INS, INS-Employers Liability, $0.00
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, INS, INS-General Liability, $0.00
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, INS, INS-Umbrella, $0.00
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, INS, INS-Other, $0.00
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, MISC, MISC-Small Tools, $0.00
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, MISC, MISC-Consumables, $0.00
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, MISC, MISC-PPE, $0.00
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, MISC, MISC-Other, $0.00
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, OP, OP-Overhead, $0.00
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, OP, OP-Profit, $0.00
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, BR, Billing Rate, $45.04
123 Enterprises, BM, GF, 1, 2-OT, <ENTER>, MULT, Multiplier, 0
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, SSM-VC, CCM-Vendor Code, <ENTER>
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, CCM-TC, CCM-Time Code, DT
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, BW-100, Base Wage-100%, $0.00
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, BW-90, Base Wage-100%, $0.00
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, FB, Vacation, $7.00
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, SUPV, Supervision Adder, n/a
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, BW-TB, Base Wage - Taxable, $7.00
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, PT, PT-FICA, $0.54
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, PT, PT-FUI, $0.04
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, PT, PT-SUI, $0.00
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, FB, FB-Health & Welfare, $8.57
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, FB, FB-Pension, $29.10
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, FB, FB-Annuity, $1.50
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, FB, FB-Apprenticeship, $2.40
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, FB, FB-Local 92 JATC, $6.00
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, FB, FB-National Annuity, $0.34
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, FB, FB-Most, $0.30
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, FB, FB-WSBIF, $0.00
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, FB, FB-N/A, $0.00
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, FB, FB-N/A, $0.00
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, INS, INS-Workers Comp, $0.00
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, INS, INS-Employers Liability, $0.00
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, INS, INS-General Liability, $0.00
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, INS, INS-Umbrella, $0.00
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, INS, INS-Other, $0.00
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, MISC, MISC-Small Tools, $0.00
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, MISC, MISC-Consumables, $0.00
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, MISC, MISC-PPE, $0.00
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, MISC, MISC-Other, $0.00
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, OP, OP-Overhead, $0.00
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, OP, OP-Profit, $0.00
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, BR, Billing Rate, $55.79
123 Enterprises, BM, GF, 1, 3-DT, <ENTER>, MULT, Multiplier, 0
];

F:

load Sum(Amount) as Amount,Company, Craft, [Craft Class], [Craft Line], [Rate Type], [CCM-Vend Cd], [Rate Component Code]
Resident T
Group by Company, Craft, [Craft Class], [Craft Line], [Rate Type], [CCM-Vend Cd], [Rate Component Code];

drop table T

[mrthomasshelby]

This works! Thanks a lot! So the ordering of the fields while doing group by is to be kept in mind.

[shiveshsingh]

My pleasure  🙂