Help needed with nested if and else statement

[#1]

I'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

[#2 · Accepted solution]

How 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
		)
	);
);

[#3]

I 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 ) ) ) ) ) );

[#4]

Here 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 ) ) ) ) ) );

[#5]

I 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 ) ) ) ) ) );

[#6]

I 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

[#7]

For 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

[#8]

This wont help because I need to exclude FS columns that are part of Cat 1 and Cat2.

Cat 1 -  

		{"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"),

Cat 2 - 

Contains( {"SS098", "SS090"}, colname ) | (colname >= "FS9700" & colname <= "FS9791") | (colname >= "FS9793" & colname <= "FS9799"),
			Insert Into( sum_lst4, col_lst2[i] )

Cat3 - all FS* cols that are of part of Cat1 and Cat2

[#9]

If the script makes it to that last Else statement where it checks for FS in the column name, by definition that column has not been included in Cat1 or Cat2. So a column will not be put into both Cat1 and Cat3. If a Cat1 column gets identified, your code inserts it into the right list and then moves to the next iteration of the For() loop, it does not keep running down the chain because it doesn't need to run the Else portion.

I would give it a try and see if it works. Apologies I am not completely clear what you are asking but hopefully this does it!

[#10]

Thanks for all the suggestions.I could finally get it working by adding two else statements and adding contains condition in the last else statement

For( i = 1, i <= N Items( col_lst2 ), i++,
	colname = Column( col_lst2[i] ) << get name;
	If(
		Contains({"FS9429", "FS9792", "FS9906","FS1156","FS1157"},colname) | (colname >= "FS800" & colname <= "FS899") |(colname >= "FS901" & colname <= "FS916") |
		(colname >= "FS954" & colname <= "FS965") |(colname >= "FS1059" & colname <= "FS1063") |(colname >= "FS1348" & colname <= "FS1363") |(colname >= "FS1374" & colname <= "FS1379") |
		 (colname >= "FS1500" & colname <= "FS1599") | (colname >= "FS2581" & colname <= "FS2586") | (colname >= "FS2900" & colname <= "FS2950") | (colname >= "FS3400" & colname <= "FS3450") |(colname >= "FS5300" & colname <= "FS5399") |
		 (colname >= "FS6700" & colname <= "FS6799"),
		Insert Into( sum_lst2, col_lst2[i] )///CAT1//
	
	, // Else	
	
		Contains( {"FS9700"}, colname ) | (colname >= "FS9701" & colname <= "FS9791") | (colname >= "FS9793" & colname <= "FS9799")
		, 
			Insert Into( sum_lst4, col_lst2[i] ) //CAT2//		
	, // Else
		Contains( colname, "FS" ) & (colname > "FS0100"),
			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 ) ) ) ) ) );