469959_en-US

liliconcom

How do you assign a variable from a list to a row in a data table?

I am working with a data table that has variable number of rows. I want to assign a categorical description that I will define to a new column called 'Reason 1'. This 'Reason 1' column will be assigned when the 'Name' column matches any of the strings in any of the lists that I have defined.

 

The 'Name' row entry will match exactly with an entry in the ::slitter, ::label, or ::case lists.

 

I want to assign the different reason 1 codes to all rows and then analyze the data according to each Reason 1.

 

dt1 << New Column("Reason 1", Character, "Nominal");

Nrows = N Rows(dt1);

For( i =1, i<=Nrows, i++
  For( j=1, j<= N items( ::slitter ), j++,
  If(:Name(i) == Eval(slitter(j)), :Reason 1(i) = "Slitter");
  );
 );


// %Slitter% description codes
slitter = {"TM2300-370",
"TM2300-380",
"TM2300-385",
"TM2300-390",
"TM2300-395",
"TM2300-400"};<BR /><BR />// %Label% description codes (8)<BR />::label = {"TM2300-020",<BR />"TM2300-025",<BR />"TM2300-070",<BR />"TM2300-075",<BR />"TM2300-080",<BR />"TM2300-085",<BR />"TM2300-090",<BR />"TM2300-095"};<BR /><BR />//%Case% description codes (7)<BR />::case = {"TM2300-030",<BR />"TM2300-035",<BR />"TM2300-040",<BR />"TM2300-045",<BR />"TM2300-050",<BR />"TM2300-055",<BR />"TM2300-060"};

Mass CustomizationWindowsRe: How do you assign a variable from a list to a row in a data table?

For whatever reason, the loop was not assigning the variable to one particular list out of 5 lists I wanted to assign. The 'Get rows where' function accomplished what I wanted. 

 

Thank you for spending time on this solution!

 

- Logan 

Re: How do you assign a variable from a list to a row in a data table?

My mind initially went to a loop to complete what I wanted to do, but thank you for this alternative solution!

 

This categorized my data exactly like I wanted.

Re: How do you assign a variable from a list to a row in a data table?

There's another way that uses get rows where and contains.  No loop required.

dt = data table("Sample Data Set-1");

// %Slitter% description codes
slitter = {"TM2300-370", "TM2300-380", "TM2300-385", "TM2300-390", "TM2300-395", "TM2300-400"};

match_rows = dt << get rows where(contains(slitter, :Name));

dt:Reason 1[match_rows] = "Slitter";

Re: How do you assign a variable from a list to a row in a data table?

You have a couple of syntax errors, and an inefficient For loop to check for matches that can be replaced by using a Contains() function.  I have also added a method for creating the new formula column to do the same calculation.

Names Default To Here( 1 );

dt1=current data table();
dt1 << New Column( "Reason 1", Character, "Nominal" );

// %Slitter% description codes
slitter = {"TM2300-370", "TM2300-380", "TM2300-385", "TM2300-390", "TM2300-395", "TM2300-400"};

Nrows = N Rows( dt1 );

For( i = 1, i <= Nrows, i++,
 If( Contains( slitter, :Name[i] ) == 1,
  :Reason 1[i] = "Slitter"
 )
);

// or
dt1 << New Column( "Reason 2",
 character,
 formula(
  If( Row() == 1,
   slitter = {"TM2300-370", "TM2300-380", "TM2300-385", "TM2300-390", "TM2300-395", "TM2300-400"}
  );
  If( Contains( slitter, :Name ) == 1,
   :Reason 2 = "Slitter"
  );
 )
);