Group the data fields from multiple indexes

Hi Team,

I have 3 different index with one transaction_id as common in all, i am generation report with fields from all 3 indices at every midnight for transactions of last 3 days.

I have different fields in all indices , to make it in one shape i have inserted all the required fields in report in every index as NULL expect the real fields in index, Then i have created Alias of all three indices and then i have used Select query with MAX aggregation for Group-by by transaction id.

This was giving me expected result for 'D-1' where there will be only one transaction for same transaction id . but it was failing for condition of 'D-3' as there was multiple transaction for same transaction id and data was mismatched in report as MAX aggregation was applied.

Please let me know if anyone aware bout same situation and knows any kind of solution.

Thanks and regards ,
Mrunalini Sinnarkar