How to identify a record where there is more than one record linked to this record

[#1]

Hello guys

Herewith my problem/question:

I am working with tripinfo. Every single trip is captured on a tripsheet.

Each trip is allocated to either one driver or two drivers.

In my load script I want to allocate a double crew tag to a tripsheet if there is more than one driver linked to that trip.

I am able to do this on the front end, but how do I achieve this in the back end (script).

Herewith my extract from the front end:

TripsheetNumber	count(DISTINCT DriverID)	Tag
543658-20150609	2	Double Crew
543659-20150609	2	Double Crew
540077-20150528	1	Single Crew

The 'Tag' column is the result I am looking for.

[MayilVahanan]

Hi

Try like this

Load TripsheetNumber, If(Count(Distinct DriverID) = 1, 'Single Crew', 'Double Crew') as Tag

From datasource

Group by TripsheetNumber;

[giakoum]

Count drivers per trip (group by trip) in the drivers table and then use the result to applymap it on the trips table.