Names Default To Here( 1 );
//热轧部分
rz01 = Open(
"$DESKTOP/热卷存货明细.xls", //热轧明细表
Worksheets( "热卷存货明细" ),
Use for all sheets( 1 ),
Concatenate Worksheets( 0 ),
Create Concatenation Column( 0 ),
Worksheet Settings(
1,
Has Column Headers( 1 ),
Number of Rows in Headers( 1 ),
Headers Start on Row( 1 ),
Data Starts on Row( 2 ),
Data Starts on Column( 1 ),
Data Ends on Row( 0 ),
Data Ends on Column( 0 ),
Replicated Spanned Rows( 1 ),
Replicated Spanned Headers( 0 ),
Suppress Hidden Rows( 1 ),
Suppress Hidden Columns( 1 ),
Suppress Empty Columns( 1 ),
Treat as Hierarchy( 0 ),
Multiple Series Stack( 0 ),
Import Cell Colors( 0 ),
Limit Column Detect( 0 ),
Column Separator String( "-" )
)
);
rz01:材料状态码 << Data Type( Character ) << Set Modeling Type( "Nominal" );
rz01 << Move Selected Columns( {:材料实际长度}, after( :WG封锁原因代码 ) );
rz01 << Clear Select << Select Rows( N Rows( rz01 ) ) << Delete Rows; //
rz01 << New Column( "厂别",
Formula(
If(
Substr( :材料号, 2, 1 ) == "2", "热轧厂",
Substr( :材料号, 2, 1 ) == "3", "热轧厂",
Substr( :材料号, 2, 1 ) == "4", "CSP",
"错误"
)
)
);
rz01 << Move Selected Columns( {:厂别}, after( :Column 1 ) );
rz02 = Open(
"$DESKTOP/热卷申报明细.xls", //申报明细表
Worksheets( "热卷申报明细" ),
Use for all sheets( 1 ),
Concatenate Worksheets( 0 ),
Create Concatenation Column( 0 ),
Worksheet Settings(
1,
Has Column Headers( 1 ),
Number of Rows in Headers( 1 ),
Headers Start on Row( 1 ),
Data Starts on Row( 2 ),
Data Starts on Column( 1 ),
Data Ends on Row( 0 ),
Data Ends on Column( 0 ),
Replicated Spanned Rows( 1 ),
Replicated Spanned Headers( 0 ),
Suppress Hidden Rows( 1 ),
Suppress Hidden Columns( 1 ),
Suppress Empty Columns( 1 ),
Treat as Hierarchy( 0 ),
Multiple Series Stack( 0 ),
Import Cell Colors( 0 ),
Limit Column Detect( 0 ),
Column Separator String( "-" )
)
);
rz02 << New Column( "类型2",
Formula(
If(
Substr( :现货申报编号, 1, 3 ) == "IQK", "已QK寻源",
:现货申请类别 == 2, "已QC寻源",
"已申报现货"
)
)
);
rz02:材料号 << Data Type( Character ) << Set Modeling Type( "Nominal" );
rz03 = Open( "$DESKTOP/留用明细.jmp" ); //留用明细表
rz03 << Row Selection( Select where( Is Missing( :留用原因 ) ) ) << delete row()
<< run forground; //删除符合条件的行
//连接表
rz04 = rz01 << Join(
With( rz03 ),
Select( all ),
SelectWith( :材料号 ),
By Matching Columns( :材料号 = :材料号 ),
Drop multiples( 0, 0 ),
Include Nonmatches( 1, 0 ),
Preserve main table order( 1 )
);
Close( rz01, nosave );
Close( rz03, nosave );
rz04:"“热卷存货明细”中的“材料号”"n << Set Name( "材料号" );
rz04:材料号 of 留用明细 << Delete Property( "Aliases" ) << Set Name( "一事一议" );
rz04 << Move Selected Columns( {:一事一议}, after( :材料状态码 ) );
rz05 = rz04 << Join(
With( rz02 ),
Select( all ),
SelectWith( :类型2 ),
By Matching Columns( :材料号 = :材料号 ),
Drop multiples( 0, 0 ),
Include Nonmatches( 1, 0 ),
Preserve main table order( 1 )
);
Close( rz02, nosave );
Close( rz04, nosave );
//生产现货类型列,按照余材原因代码
rz05 << New Column( "现货类型",
Formula(
If(
Length( :WG余材原因代码 ) == 8, "质量原因",
Length( :WG余材原因代码 ) == 0, "质量原因",
Contains(
{"8C", "8G", "8R", "8Y", "9C", "82", "87", "90", "91", "92",
"93", "95", "96", "97", "99"},
:WG余材原因代码
), "生产原因",
Contains(
{"0H", "00", "8B", "8Z", "9A", "9B", "9D", "9E", "9F", "11",
"12", "13", "14", "24", "70", "77", "84", "94", "98"},
:WG余材原因代码
), "质量原因",
"错误"
)
)
);
rz05 << Move Selected Columns( {:现货类型}, after( :一事一议 ) );