Merging Multiple rows

[Tim_D_Baker]

Our clients (A disability support organisation) may provide more than one service to a client - however if client only use service number 6 they would like to have a check box ticked saying "No marketing"

Source table is MS SQL with 400 rows with fields:

Client_ID: can have multiple records with same Client_ID

Service_ID: each Service_ID is a unique. There are 8 Service_IDs from numbered 0 to 7. (what the Service_ID number means is another table)

What I need is logic that says if Client only has one service and its Service_ID is 6 then check the check box in destination database else uncheck.

So far using Talend Open Source I have an tDBInput to the source database > tmap > tDBOutput (for testing logic) which will eventually be a SaleforceOutPut when the logic works.

Any help would be most appreciated - having spent any hours googling and testing without result

The logic I need must be able to amalgamate all the rows in one table with the same Customer_ID to one row that looks like Customer_ID, Service_1 (Yes or No), Service_2 (Yes or No), Service_3 (Yes or No),  etc in an new array/temp table or whatever tool is used to do that.

The expression to set the Donot Market Check Box will look like Service1=="No" &&Service2=="No"&&Service3=="No"&&Service4=="No"&&Service5=="No"&&Service6=="Yes"&&Service7=="No"&& Etc ?true:false

[Anonymous]

Has this check box already existed on destination component? or you expect a new feature?

[Tim_D_Baker]

Have created the checkbox in sales force

[Anonymous]

If this is one field with boolean type in target DB or object, set the expression on tMap like this:

row1.Service_ID ==6?true:false

[Tim_D_Baker]

Thanks Shong

I know how to set the CheckBox value how ever the logic need to say the Service_ID only equals 6 and not other Service_IDs on other rows - all the rows with the same Customer_ID need to be amalgamated to one row that looks like Customer_ID, Service_1 (Yes or No), Service_2 (Yes or No), Service_3 (Yes or No),  etc

so the express will look like Service1==No &&Service2=="No"&&Service3=="No"&&Service4=="No"&&Service5=="No"&&Service6=="Yes"&&Service7=="No"&& Etc ?true:false

[Anonymous]

Hi

Can you show me some example data? Can you take a look at the tDenormalize component? It might be useful for you to amalgamated all the rows with the same Customer_ID to be one row.

Regards

Shong

[Tim_D_Baker]

Source Table is called client.service.

The rows are: (Note the Primary Key is an ID field for each row)

clientID serviceID

3 2

3 1

3 6

3 1

4 2

4 1

4 2

4 2

4 2

4 6

4 6

4 2

4 4

4 1

5 2

5 1

5 2

5 2

5 4

6 1

6 2

6 2

6 6

6 7

6 6

6 4

8 2

8 1

8 6

8 5

8 6

8 4

8 1

10 2

10 1

10 2

10 2

10 6

10 5

[Anonymous · Accepted solution]

@Not defined Not defined​ , from your source data, it seems that the check box will be true only when there is just one row for one client ID and service ID is 6.

I have an idea, count the total number of rows for each ​clientID, filter the clientID when the num_of_rows is 1, then only check if service_id==6?

source data-->tAggregageRow---tFilteRow-->tMap--->get the Client_ID whose check box will be set as true.

[Tim_D_Baker]

Thank you Shong for your guidance. I created temp column in the AggregateRow Schema called Count.

I can now filter the results of AggregateRow_1 by Count == 1 && Service_ID == 6