Right set of transform for actions based on satisfying multiple conditions.

[Qunovaganesh]

Hi Enthisiasts,

Greetings. We are having a use case here. Request if you could suggest the right set of transforms for doing this.
Please consider that we have 2 files with identical headers to compare and basis the match or otherwise of two or more fields in each of the files, we want to send the records to two different buckets.

We have tried filter transforms. But it is a step by step approach and hence doesn't work. eg: If the name matches, the filter transform sends the record to bucket 1. But what if the GST and phone for this record doesn't match. And in this case, I would want to send it to bucket 2.

We want to send the records to the buckets basis the column scenario below.

We tried Merge rows Diff transform too. But unable to get the desired output mentioned in the action column.

Your guidance shall go a long way.

Regards
Ganesh

[dave-csc]

Probably not the most efficient way, but I would try this, provided that the data files include a primary key in other fields (different from Outlet name, GST and Phone):

1. Read the two files and sort each one by their primary key, using Sort rows
2. Merge the sorted streams using a Merge join transform, use the primary key as join field: this will give you the corresponding rows attached together (in step 5 you can eventually strip out the duplicate columns). An inner join would merge only the data that have the same primary key in both files, if you want to keep those in the first (or second) file anyway, use a left (or right) join.
3. Add a User Defined Java Expression transform, and add three Boolean fields with expressions like outlet_name.equals(outlet_name_1) (use the expression editor to have a guide) and name like flag_outlet_name
4. In the same latter transform add another Boolean field named send_to_bucket, the expression will be something like (flag_outlet_name && flag_gst) || (flag_gst && flag_phone)
5. You may want to add a Select values transform, to strip out all the useless fields you do not need in the buckets
6. Finally, link to a Filter rows and evaluate send_to_bucket: true (Y) will be sent to bucket 1, false (N) to bucket 2

Hope this helps