Help needed with nested if and else statementI'm using if else statement to bucket certain FS*/SS* as CAT1 and CAT2 .All remaining FS*/SS* that do not belong to CAT1/CAT2 need to be classified as CAT3.
Having trouble with writing else part for CAT3.Any help appreciated.
cdt = open("temp\data.csv");
cdt=current data table();
sum_lst2 = {};
sum_lst3 = {};
sum_lst4 = {};
col_lst2 = cdt << get column names( Numeric );
For( i = 1, i <= N Items( col_lst2 ), i++,
colname = Column( col_lst2[i] ) << get name;
If( Contains( {"SS008","SS009","SS010","SS015","SS053","FS9429","FS9792","FS9906","FS9908", "FS1302","FS1305","FS1308","FS1360","FS1373","FS2744","FS4621","FS4625","FS4630","FS4640","FS4681","FS4830"}, colname ) | (colname >= "FS2900" & colname <= "FS2950") | (colname >= "FS3400" & colname <= "FS3450"),
Insert Into( sum_lst2, col_lst2[i] )
); ///Cat1//
If( Contains( {"SS098","SS090"}, colname ) | (colname >= "FS9700" & colname <= "FS9791") | (colname >= "FS9793" & colname <= "FS9799"),
Insert Into( sum_lst4, col_lst2[i] )
);//Cat2//
If( Contains( {"SS019","SS027","SS035","FS9901"}, colname ) | (colname >= "FS3451" & colname <= "FS3499"),
Insert Into( sum_lst3, col_lst2[i] )
);//Cat3
);| Item | FS0100 | FS0847 | FS0875 | FS0876 | FS0877 | FS1059 | FS1061 | FS1090 | FS1315 | FS1501 | FS1503 | FS1505 | FS1537 | FS1539 | FS1541 | FS1543 | FS1559 | FS1590 | FS1930 | FS1933 | FS2706 | FS2740 | FS2742 | FS2745 | FS3416 | FS3417 | FS3418 | FS3422 | FS3423 | FS3455 | FS3469 | FS3471 | FS3492 | FS3493 | FS5391 | FS9429 | FS9792 | FS9901 | FS9906 | SS001 | SS008 | SS010 | SS013 | SS015 | SS019 | SS027 | SS034 | SS053 | SS094 | SS097 | SS099 |
| 1 | 132 | 1 | | | | | | | | | | | | | | | | | 3 | | | | | | | | | | | | | | | | | | | | 1 | 132 | 1 | | | | 3 | | | | | | 1 |
| 2 | 600 | 2 | | | | | | | | | | | | | | | | | 7 | 2 | | 1 | 4 | | | | 5 | | | 1 | 4 | | | | | 1 | 1 | 1 | 2 | 600 | 2 | | 0 | | 9 | 5 | 10 | | 1 | 1 | 3 |
| 3 | 562 | | | | | 1 | | | | | | | | | | | | | 9 | | | 1 | 1 | | | 1 | 1 | 1 | 2 | 1 | 1 | 1 | 1 | 2 | | | | 2 | 1 | 562 | | 1 | | | 9 | 2 | 11 | | | | 3 |
| 4 | 527 | 2 | | 2 | 1 | | 2 | 1 | | 7 | | 2 | 4 | 3 | 3 | 2 | | 24 | 18 | 2 | 3 | | | 1 | 2 | | 1 | | 1 | | 4 | | 1 | 1 | 1 | 3 | | | 2 | 527 | 5 | 3 | | 45 | 20 | 4 | 10 | 1 | 3 | | 2 |
| 5 | 322 | | 1 | | | | | | 1 | | 2 | | | | | | 1 | | 7 | | | | 3 | | | | | | | | | | | 2 | | | | | 3 | 322 | 1 | | 1 | 3 | 7 | 3 | 2 | | | 0 | 3 |
ScriptingRe: Help needed with nested if and else statementHow about something like the below? I didn't actually see any "else" statements in your script since you used the glue operator between If() statements. See below where I have nested all your If() statements and then had the final else as the Insert Into the sum_lst4 list (assuming that was where you wanted the Cat4 column names.
cdt = Current Data Table();
sum_lst2 = {};
sum_lst3 = {};
sum_lst4 = {};
col_lst2 = cdt << get column names( Numeric );
For( i = 1, i <= N Items( col_lst2 ), i++,
colname = Column( col_lst2[i] ) << get name;
If(
Contains(
{"SS008", "SS009", "SS010", "SS015", "SS053", "FS9429", "FS9792", "FS9906", "FS9908", "FS1302", "FS1305", "FS1308", "FS1360", "FS1373",
"FS2744", "FS4621", "FS4625", "FS4630", "FS4640", "FS4681", "FS4830"},
colname
) | (colname >= "FS2900" & colname <= "FS2950") | (colname >= "FS3400" & colname <= "FS3450"),
Insert Into( sum_lst2, col_lst2[i] ), ///Cat1//
If( Contains( {"SS098", "SS090"}, colname ) | (colname >= "FS9700" & colname <= "FS9791") | (colname >= "FS9793" & colname <= "FS9799"),
Insert Into( sum_lst4, col_lst2[i] ), //Cat2//
If( Contains( {"SS019", "SS027", "SS035", "FS9901"}, colname ) | (colname >= "FS3451" & colname <= "FS3499"),
Insert Into( sum_lst3, col_lst2[i] ),
Insert Into( sum_lst4, col_lst2[i] )///final nested else statement for Cat4
);//Cat3
)
);
);Re: Help needed with nested if and else statementI mistakenly copied wrong code in my question.Below is the code I wrote for if/else implementation but column CAT3 only has zeros.Is there an issue with the placement of else statement.
To reiterate I'm trying to bucket this into 2 categories,whatever values do not belong to CAT1/2 need to be bucketed as CAT3.
cdt = open("temp\Rawdata.csv");
cdt = Current Data Table();
sum_lst2 = {};
sum_lst3 = {};
sum_lst4 = {};
col_lst2 = cdt << get column names( Numeric );
For( i = 1, i <= N Items( col_lst2 ), i++,
colname = Column( col_lst2[i] ) << get name;
If(
Contains(
{"SS008", "SS009", "SS010", "SS015", "SS053", "FS9429", "FS9792", "FS9906", "FS9908", "FS1302", "FS1305", "FS1308", "FS1360", "FS1373",
"FS2744", "FS4621", "FS4625", "FS4630", "FS4640", "FS4681", "FS4830"},
colname
) | (colname >= "FS2900" & colname <= "FS2950") | (colname >= "FS3400" & colname <= "FS3450"),
Insert Into( sum_lst2, col_lst2[i] ), ///CAT1//
If( Contains( {"SS098", "SS090"}, colname ) | (colname >= "FS9700" & colname <= "FS9791") | (colname >= "FS9793" & colname <= "FS9799"),
Insert Into( sum_lst4, col_lst2[i] ), //CAT2//
),
Insert Into( sum_lst3, col_lst2[i] )///CAT3
)
);
Eval( Eval Expr( cdt << New Column( "CAT1", formula( Sum( Expr( sum_lst2 ) ) ) ) ) );
Eval( Eval Expr( cdt << New Column( "CAT3", formula( Sum( Expr( sum_lst3 ) ) ) ) ) );
Eval( Eval Expr( cdt << New Column( "CAT2", formula( Sum( Expr( sum_lst4 ) ) ) ) ) );Re: Help needed with nested if and else statementHere is my reworking of your code, and it properly handles all 51 columns. It inserts 12 columns into sum_lst2, 39 into sum_lst3 and zero into sum_lst4
Names Default To Here( 1 );
//cdt = open("temp\Rawdata.csv");
cdt = Current Data Table();
sum_lst2 = {};
sum_lst3 = {};
sum_lst4 = {};
col_lst2 = cdt << get column names( Numeric);
For( i = 1, i <= N Items( col_lst2 ), i++,
colname = Column( col_lst2[i] ) << get name;
If(
Contains(
{"SS008", "SS009", "SS010", "SS015", "SS053", "FS9429", "FS9792", "FS9906", "FS9908", "FS1302", "FS1305",
"FS1308", "FS1360", "FS1373", "FS2744", "FS4621", "FS4625", "FS4630", "FS4640", "FS4681", "FS4830"},
colname
) | (colname >= "FS2900" & colname <= "FS2950") | (colname >= "FS3400" & colname <= "FS3450")
,
Insert Into( sum_lst2, col_lst2[i] )///CAT1//
, // Else
Contains( {"SS098", "SS090"}, colname ) | (colname >= "FS9700" & colname <= "FS9791") | (colname >= "FS9793" &
colname <= "FS9799")
,
Insert Into( sum_lst4, col_lst2[i] ) //CAT2//
, // Else
Insert Into( sum_lst3, col_lst2[i] )///CAT3
);
);
Eval( Eval Expr( cdt << New Column( "CAT1", formula( Sum( Expr( sum_lst2 ) ) ) ) ) );
Eval( Eval Expr( cdt << New Column( "CAT3", formula( Sum( Expr( sum_lst3 ) ) ) ) ) );
Eval( Eval Expr( cdt << New Column( "CAT2", formula( Sum( Expr( sum_lst4 ) ) ) ) ) );
Re: Help needed with nested if and else statementI think you are very close! You closed off that second If() statement a bit too soon. I get all zeros for CAT2 but CAT3 has values now. If you are expecting values for CAT2 at this point then it may need a bit more work.
cdt = Current Data Table();
sum_lst2 = {};
sum_lst3 = {};
sum_lst4 = {};
col_lst2 = cdt << get column names( Numeric );
For( i = 1, i <= N Items( col_lst2 ), i++,
colname = Column( col_lst2[i] ) << get name;
If(
Contains(
{"SS008", "SS009", "SS010", "SS015", "SS053", "FS9429", "FS9792", "FS9906", "FS9908", "FS1302", "FS1305", "FS1308", "FS1360", "FS1373",
"FS2744", "FS4621", "FS4625", "FS4630", "FS4640", "FS4681", "FS4830"},
colname
) | (colname >= "FS2900" & colname <= "FS2950") | (colname >= "FS3400" & colname <= "FS3450"),
Insert Into( sum_lst2, col_lst2[i] ), ///CAT1//
If( Contains( {"SS098", "SS090"}, colname ) | (colname >= "FS9700" & colname <= "FS9791") | (colname >= "FS9793" & colname <= "FS9799"),
Insert Into( sum_lst4, col_lst2[i] ), //CAT2//
Insert Into( sum_lst3, col_lst2[i] )///CAT3
)
)
);
Eval( Eval Expr( cdt << New Column( "CAT1", formula( Sum( Expr( sum_lst2 ) ) ) ) ) );
Eval( Eval Expr( cdt << New Column( "CAT3", formula( Sum( Expr( sum_lst3 ) ) ) ) ) );
Eval( Eval Expr( cdt << New Column( "CAT2", formula( Sum( Expr( sum_lst4 ) ) ) ) ) );Re: Help needed with nested if and else statementI figured what the issue is - For Cat3 I'm getting a huge value(in range e15 after fixing column format).The reason is I have a few more columns in the table.
For Cat3 along with leftover FS*,my script is adding up values from other columns.
In the else statement how do I consider only the columns with column names FS*?
| Item | Location | Oper | TOTAL_TESTED | TOTAL_GOOD | TOTAL_BAD | Test_date | Pkgd | Drs | Prgnm | Week | FS0100 | FS0847 | FS0875 | FS0876 | FS0877 | FS1059 | FS1061 | FS1090 | FS1315 | FS1501 | FS1503 | FS1505 | FS1537 | FS1539 | FS1541 | FS1543 | FS1559 | FS1590 | FS1930 | FS1933 | FS2706 | FS2740 | FS2742 | FS2745 | FS3416 | FS3417 | FS3418 | FS3422 | FS3423 | FS3455 | FS3469 | FS3471 | FS3492 | FS3493 | FS5391 | FS9429 | FS9792 | FS9901 | FS9906 |
| | | | | | | | | | | | 132 | 1 | | | | | | | | | | | | | | | | | 3 | | | | | | | | | | | | | | | | | | | | 1 |
| | | | | | | | | | | | 600 | 2 | | | | | | | | | | | | | | | | | 7 | 2 | | 1 | 4 | | | | 5 | | | 1 | 4 | | | | | 1 | 1 | 1 | 2 |
| | | | | | | | | | | | 562 | | | | | 1 | | | | | | | | | | | | | 9 | | | 1 | 1 | | | 1 | 1 | 1 | 2 | 1 | 1 | 1 | 1 | 2 | | | | 2 | 1 |
| | | | | | | | | | | | 527 | 2 | | 2 | 1 | | 2 | 1 | | 7 | | 2 | 4 | 3 | 3 | 2 | | 24 | 18 | 2 | 3 | | | 1 | 2 | | 1 | | 1 | | 4 | | 1 | 1 | 1 | 3 | | | 2 |
| | | | | | | | | | | | 322 | | 1 | | | | | | 1 | | 2 | | | | | | 1 | | 7 | | | | 3 | | | | | | | | | | | 2 | | | | | 3 |
Re: Help needed with nested if and else statementFor that last Else statement that you have (snippet shown below):
Insert Into( sum_lst3, col_lst2[i] )///CAT3
How about just adding one last nested If() statement like below? Then don't have an Else statement
If(
Contains( colname, "FS" ),
Insert Into( sum_lst3, col_lst2[i] )
)///CAT3, no ELSE statement
