Merge table when 3 fields values matched

[Sara_3]

I have  table 1 , the key field is ID and Category type is "5" with fields .

table 1:

Load 

ID ,

Purchase Order Number ,

Part Number ,

Batch Number ,

Category Type ,

Vendor Number ,

Date

from AAAA

where Category Type  = "5";

Need Help to merge data:

I wanted to merge Category type 9s  and Category type 1s data into Category type 5s (Table 1 ) by matching with “Purchase Order Number/Part Number /Batch Number”, take min of matching data, and format new column as a min date. data source is same "AAAA".

table 1:

ID	Purchase Order Number	Part Number	Batch Number	Category Type	Vendor Number	Date
1	123	AS	1	5	243143	march 2022
2	234	CD	5	5	657658	feb 2001
3	456	BD	6	5	675678	june 2022

[Digvijay_Singh]

May be try something like this,  you may need little tweak for your needs if I have missed something - 

1. First take all needed categories 1,5,9 in a load

2. For matching values of Purchase order no, Part # and Batch # take the record with min date using firstsortedvalue.

Table1:
Load
*
Where Match([Category Type],5,1,9)
;
Load * inline [
ID,Purchase Order #, Part #, Batch #, Category Type, Vendor #, Date
1, 123, AS,1,1,243143,02/15/2023
2, 123, AS,1,5,111111,01/15/2023
3, 123, AS,1,9,243000,03/15/2023
4, 234, CD,5,1,111111,06/15/2023
5, 234, CD,5,5,865497,08/15/2023
6, 234, CD,5,9,675831,11/15/2023
7, 456, BD,6,1,129856,07/15/2023
8, 456, BD,6,9,111111,04/15/2023
9, 456, BD,6,5,675831,09/15/2023
];

NoConcatenate
Final:
Load FirstSortedValue(ID, Date) as ID,
[Purchase Order #],
[Part #],
[Batch #],
FirstSortedValue([Category Type], Date) as [Category Type],
FirstSortedValue([Vendor #], Date) as [Vendor #],
FirstSortedValue(Date, Date) as [Min Date]
resident Table1
Group By [Purchase Order #],
[Part #],
[Batch #];

Drop table Table1;

[Sara_3]

Thanks Digvijay , I will try this.