- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Select rows that match any value in a list
I was having an issue with a more complicated script and wrote a simple test script to try and fix the issue, but can't figure out what's wrong. It's probably glaringly obvious, but I've been looking at it for too long to notice. I create a table with :Disposition Type, :NC Code, and :NC Qty. The original script takes user input to create the list of codes, but here I predefined the list. I want to create a column for each code listed in the codes list that copies over the value in :NC Qty if :NC Code matches the code from the list. It recognizes codes[c] to name the column, but not in the column formula. I've also tried using Contains instead of == in the formula, but that didn't seem to make a difference.
New Table( "NC Qty Col Test",
Add Rows( 5 ),
New Column( "Disposition Type",
Character,
"Nominal",
Set Values(
{"SCRAP-R", "SCRAP-DNR", "DEVIATE-MATERIAL", "DEVIATE-PROCESS",
"DEVIATE-PRODUCT"}
)
),
New Column( "NC Code",
Character,
"Nominal",
Set Values( {"007", "014", "020", "007", "008"} )
),
New Column( "NC Qty",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [1, 2, 3, 4, 5] )
)
);
codes = {"007", "014"};
For(c=1, c<=NItems(codes), c++,
New Column("Code " || codes[c] || " NC Qty", Numeric, Continuous, Formula(If(:NC Code==codes[c], :NC Qty, 0 )));
);
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Select rows that match any value in a list
The issue is that your value of "c" changes, and is not static within the formula definition. Therefore, you need to force the code to have a static value of "codes[c]" in the formula. Below is one way to do that:
New Table( "NC Qty Col Test",
Add Rows( 5 ),
New Column( "Disposition Type",
Character,
"Nominal",
Set Values( {"SCRAP-R", "SCRAP-DNR", "DEVIATE-MATERIAL", "DEVIATE-PROCESS", "DEVIATE-PRODUCT"} )
),
New Column( "NC Code", Character, "Nominal", Set Values( {"007", "014", "020", "007", "008"} ) ),
New Column( "NC Qty", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2, 3, 4, 5] ) )
);
codes = {"007", "014"};
For( c = 1, c <= N Items( codes ), c++,
Eval(
Substitute(
Expr(
New Column( "Code " || codes[c] || " NC Qty", Numeric, Continuous, Formula( If( :NC Code == __code__, :NC Qty, 0 ) ) )
),
Expr( __code__ ), codes[c]
)
)
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Select rows that match any value in a list
This one is a little tricky. When you use variables in a formula you have to initialize them inside the formula. But I did it without using formulas.
dt = New Table( "NC Qty Col Test", Add Rows( 5 ),
New Column( "Disposition Type", Character, "Nominal",
Set Values(
{"SCRAP-R", "SCRAP-DNR", "DEVIATE-MATERIAL", "DEVIATE-PROCESS",
"DEVIATE-PRODUCT"}
)
),
New Column( "NC Code", Character, "Nominal",
Set Values( {"007", "014", "020", "007", "008"} )
),
New Column( "NC Qty", Numeric, "Continuous",
Format( "Best", 12 ),
Set Values( [1, 2, 3, 4, 5] )
)
);
codes = {"007", "014"};
For(c = 1, c <= NItems(codes), c++,
dt << New Column("Code " || codes[c] || " NC Qty", numeric, "Continuous")
);
for (k = 1, k <= nitems(codes), k++,
r = dt << get rows where(:NC Code == codes[k]);
colname = "Code " || codes[k] || " NC Qty";
if (nrows(r) > 0,
for (i = 1, i <= nrows(r), i++,
one_row = r[i];
column(dt, colname)[one_row] = column(dt, "NC Qty")[one_row];
);
);
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Select rows that match any value in a list
The issue is that your value of "c" changes, and is not static within the formula definition. Therefore, you need to force the code to have a static value of "codes[c]" in the formula. Below is one way to do that:
New Table( "NC Qty Col Test",
Add Rows( 5 ),
New Column( "Disposition Type",
Character,
"Nominal",
Set Values( {"SCRAP-R", "SCRAP-DNR", "DEVIATE-MATERIAL", "DEVIATE-PROCESS", "DEVIATE-PRODUCT"} )
),
New Column( "NC Code", Character, "Nominal", Set Values( {"007", "014", "020", "007", "008"} ) ),
New Column( "NC Qty", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2, 3, 4, 5] ) )
);
codes = {"007", "014"};
For( c = 1, c <= N Items( codes ), c++,
Eval(
Substitute(
Expr(
New Column( "Code " || codes[c] || " NC Qty", Numeric, Continuous, Formula( If( :NC Code == __code__, :NC Qty, 0 ) ) )
),
Expr( __code__ ), codes[c]
)
)
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Select rows that match any value in a list
This one is a little tricky. When you use variables in a formula you have to initialize them inside the formula. But I did it without using formulas.
dt = New Table( "NC Qty Col Test", Add Rows( 5 ),
New Column( "Disposition Type", Character, "Nominal",
Set Values(
{"SCRAP-R", "SCRAP-DNR", "DEVIATE-MATERIAL", "DEVIATE-PROCESS",
"DEVIATE-PRODUCT"}
)
),
New Column( "NC Code", Character, "Nominal",
Set Values( {"007", "014", "020", "007", "008"} )
),
New Column( "NC Qty", Numeric, "Continuous",
Format( "Best", 12 ),
Set Values( [1, 2, 3, 4, 5] )
)
);
codes = {"007", "014"};
For(c = 1, c <= NItems(codes), c++,
dt << New Column("Code " || codes[c] || " NC Qty", numeric, "Continuous")
);
for (k = 1, k <= nitems(codes), k++,
r = dt << get rows where(:NC Code == codes[k]);
colname = "Code " || codes[k] || " NC Qty";
if (nrows(r) > 0,
for (i = 1, i <= nrows(r), i++,
one_row = r[i];
column(dt, colname)[one_row] = column(dt, "NC Qty")[one_row];
);
);
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Select rows that match any value in a list
Thanks so much for helping me figure it out! Either of those options would be great to fix my script!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Select rows that match any value in a list
I was able to use the proposed solution to fix my original question and it seemed to make sense. However, then I realized that I had another portion of the same large script that does a very similar thing to create columns based on a variable value from a list. What I don't understand is why one works and the other doesn't. Here's the other section of script. In this portion I have a cost associated with the NC Code that is only supposed to be applied for Disposition Types of SCRAP-R and SCRAP-DNR. This cost is listed in the Total Cost column.
For(c=1, c<=NItems(codes), c++,
CF<<New Column("Code " || codes[c] || " Cost", Numeric, Continuous, Format(Currency), Formula(If(And(:NC Code == char(codes[c]), :Disposition Type == "SCRAP-R"), :NC Qty with Zeros * :Total Cost,
And(:NC Code == char(codes[c]), :Disposition Type == "SCRAP-DNR"), :NC Qty with Zeros * :Total Cost,
And(:NC Code == char(codes[c]), :Disposition Type == "REWORK"), 0,
And(:NC Code == char(codes[c]), :Disposition Type == "DEVIATE-MATERIAL"), 0,
And(:NC Code == char(codes[c]), :Disposition Type == "DEVIATE-PROCESS"), 0,
And(:NC Code == char(codes[c]), :Disposition Type == "DEVIATE-PRODUCT"), 0,
0
);
););
);
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Select rows that match any value in a list
The issue is the same as it was in the first case. codes[c] will not be interpreted in the formua. You have to substitute the actual value for codes[c] into the formula.
For( c = 1, c <= N Items( codes ), c++,
Eval(
Substitute(
Expr(
CF << New Column( "Code " || codes[c] || " Cost",
Numeric,
Continuous,
Format( Currency ),
Formula(
If(
And( :NC Code == __code__, :Disposition Type == "SCRAP-R" ),
:NC Qty with Zeros * :Total Cost,
And( :NC Code == __code__, :Disposition Type == "SCRAP-DNR" ),
:NC Qty with Zeros * :Total Cost,
And( :NC Code == __code__, :Disposition Type == "REWORK" ), 0,
And( :NC Code == __code__, :Disposition Type == "DEVIATE-MATERIAL" ), 0,
And( :NC Code == __code__, :Disposition Type == "DEVIATE-PROCESS" ), 0,
And( :NC Code == __code__, :Disposition Type == "DEVIATE-PRODUCT" ), 0,
0
)
)
)
),
Expr( __code__ ), codes[c]
)
)
);