topic Re: Project scenarios in Talend Studio

Project scenarios

Prasanna3 — Sun, 26 Aug 2018 04:29:00 GMT

Hi

Can you please provide me solution for the below scenario?

Input:

Stingid Stringidentifier Fiscalyear Quarter1 Salesvolume1 Quarter2 Salesvolume2 Quarter3 Salesvolume3 Quarter4 Salesvolume4
14360 Newyork FY-2014 Q1 250 Q2 350 Q3 450 Q4 450
14360 London FY-2014 Q1 350 Q2 450 Q3 50 Q4 56
15602 Newyork FY-2015 Q1 400 Q2 500 Q3 30 Q4 100
15602 London FY-2015 Q1 200 Q2 600 Q3 10 Q4 150

Output:

Stingid Fiscalyear Quarter Salesvolume
14360 FY-2014 Q1 sum value
14360 FY-2014 Q2 sum value
14360 FY-2014 Q3 sum value
14360 FY-2014 Q4 sum value

Re: Project scenarios

TRF — Sun, 26 Aug 2018 07:32:10 GMT

This solution should works:
tFileInputDelimited-->tSplitRow-->tAggregateRow-->tLogRow
For tSplitRow define the schema with the following fields:
- Stringid
- Fiscalyear
- Quarter
- Salesvolume (Integer or any numeric datatype)
Add 4 lines for the "Columns mapping" section and complete these lines like this:
- Line 1
- row1.Stringid
- row1.Fiscalyear
- row1.Quarter1
- row1.Salesvolume1
- Line 2
- row1.Stringid
- row1.Fiscalyear
- row1.Quarter2
- row1.Salesvolume2
And so on for lines 3 and 4.
At this stage the input flow contains 4 lines for each line of the original flow.
For tAggregateRow the schema must be the same as for tSplitRow with the same datatypes.
Add the following columns for the "Group by" section (same name for input and output):
- Stringid
- Fiscalyear
- Quarter
Add the following line for the "Operations" section:
- Output column: Salesvolume
- Function: sum
- Input column position: Salesvolume
- Ignore null values: ticked
This will sum Salesvolume values for all rows with the same Stringid, Fiscalyear and Quarter.

Re: Project scenarios

Prasanna3 — Sun, 26 Aug 2018 08:15:18 GMT

Thanks for reply.

I will do this and get back to you

Re: Project scenarios

Prasanna3 — Tue, 28 Aug 2018 05:33:42 GMT

Hi
This works well.
But if i want to add string identifier column in my output how to do that?
Output:

Stingid Stringidentifier Fiscalyear Quarter Salesvolume
14360 Newyork FY-2014 Q1 600
14360 Newyork FY-2014 Q2 800
15602 London FY-2015 Q1 600
15602 London FY-2015 Q2 506

Re: Project scenarios

TRF — Tue, 28 Aug 2018 14:07:54 GMT

The scenario has changed...
Add this field to tSplitRow and tAggregateRow group by, but the output will be a little bit different of what you asked first as you'll have lines for both Newyork and London.

Re: Project scenarios

Prasanna3 — Tue, 28 Aug 2018 14:17:27 GMT

Hi,

We will get duplicates then how we can eliminate