Filtering the data from table consist of Line Number

[Sara_3]

Sample data:

Part Number -ID consist of Part number and ID	Line Number	Part Type	Line Number Date
234-22	10	678	2-4-2001
789-22	10	100	2-4-2001
234-22	20	909	2-4-2001
234-22	30	678	2-4-2001
987-22	30	100	2-4-2001
122-22	80	678	5-8-2006
122-22	80	100	8-8-2006

OUTPUT:

data consist of sample data (red and blue font color) of distinct Part Numbers.

Part Number -ID	Lowest (Line Number 678) if more than 1 of distinct Part number	Line Number 100	Flag if same Line number has 100 Part Type	Part Number of 100
234-22	10	10	yes	789
122-22	80	80	yes	122

Explanation of Output data table:

(Column 1) --in the above Sample data we have 2 distinct Part numbers with 678 Part type: 234 and 122.

(Column 2)--but in 234 we have 2 lines of Part type 678 so we picked the lowest Line number like 10 not 30.

(Column 3)--then we picked the Line number 10 and looking for same line number in Part type 100 . yes it has; line number 10 with Part type 100.

(Column 4)-- flag if the lowest line number of Part type 678 has the same Line number of Part type 100 .

(Column 5)--  Part Number of Part type 100.

Thanks!

[vchuprina]

Hi, 

Please try the following script

TMP:
LOAD * Inline[
Part Number -ID, Line Number,  Part Type, Line Number Date    
234-22,   10,  678, 2-4-2001 
789-22,   10,  100, 2-4-2001 
234-22,   20,  909, 2-4-2001 
234-22,   30,  678, 2-4-2001 
987-22,   30,  100, 2-4-2001 
122-22,   80,  678, 5-8-2006 
122-22,   80,  100, 8-8-2006 
];

Output:
LOAD
     [Part Number -ID],
     Min([Line Number]) AS [Lowest (Line Number 678)]
Resident TMP
Where [Part Type] = '678'
Group By [Part Number -ID];

Left Join(Output)
LOAD
     [Line Number] AS [Line Number 100],
     [Line Number] AS [Lowest (Line Number 678)],
     SubField([Part Number -ID], '-', 1) AS [Part Number of 100],
     'yes' AS [Flag if same Line number has 100 Part Type]
Resident TMP
Where [Part Type] = '100';

DROP Table TMP;

Result:

Regards,

Vitalii