display aggregate function output in trailer record

Question

We are reading csv file input which has two fields. Sample input file is given for reference

source, balance
1,100
2, 200

I need to create a trailer record in the output that the following details-

• number of records in the csv file(excluding file header)
• sum of values in column "balance" of the input file

In our case, the trailer record will be -

Trailer 02   300

How can I achieve this in data factory?

Answer 1

I have repro’d with your sample data and was able to add the trailer record with aggregated values using Data flow.

1. Add source1 with the source data set.

2. Add a derived column transformation to create a dummy column with a dummy value.

3. Add pivot transformation to get the aggregate value for trailer record.

concat('Trailer ',toString(count(source)),' ',toString(sum(toInteger(balance))))

4. Add source2 with the same source dataset as source 1.

5. Add union transformation to source2 and select pivot transformation as union with the stream to combine trailer record from pivot with source dataset of source2.

6. Add sink transformation after the union and in settings select output to single file and provide an output filename.

Sink preview:

Answer 2

You could use some nearby SQL resource to do this kind of formatting with CTEs and UNION ALL. I am just using dummy data here to demonstrate the principle, but in terms of adding a trailing record, calculating the trailer records dynamically etc then something like this would work:

;WITH cte ( xsort, source, balance ) AS (
SELECT
10,
'1', 100 
UNION ALL
SELECT 20, '2', 200
)
SELECT source, balance
FROM
    (
    SELECT xsort, source, balance
    FROM cte
    UNION ALL
    SELECT 
        30, 
        CONCAT( 'Trailer ',
            FORMAT( ( SELECT COUNT(*) FROM cte ), '00' ),
            ' ',
            ( SELECT SUM(balance) FROM cte ) 
            ) x, 
        NULL
    ) x
ORDER BY xsort;