Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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