I wish that i could, but my company only has SAS JMP licenses. I'm not certain if i can convince my boss to go with a SAS license, since i would be the only person that would have it. Parent Component 1 2 1 3 1 4 1 5 5 6 6 7 I am running the code below in JMP, The code and log are below, but I am getting the following: Parent Component SubparentID2 1 2 * 1 3 * 1 4 * 1 5 * And am trying to get this: Parent Component 1 2 1 3 1 4 1 5 1 6 1 7 Snums = Open Database( **odbc connection string ** "SELECT [dbo].[BOM].[PARENT] as PARENTID ,[dbo].[BOM].[COMPONENT] as SUBPARENTID FROM [dbo].[BOM] --as tb1 --LEFT JOIN [dbo].[BOM] ON [dbo].[BOM].[PARENT] = [dbo].[BOM].[COMPONENT]" ); //Creates the new column Snums << New Column( "SUBPARENTID2", Numeric ); /*Loops SUBPARENTID to check all rows for matches against PARENTID. Where a match is found the value in SUBPARENTID is assigned to SUBPARENT2ID at the row containing the value used to initiate the match adds 9999*/ For( i = 1, i <= N Rows( Snums ), i++, r = Snums << Get Rows Where( :PARENTID == :SUBPARENTID[i] ); If( N Rows( r ) == 0, Continue(), N Rows( r ) == 1 & r[1] == i, :SUBPARENTID2[i] = 0, N Rows( r ) == 1, :SUBPARENTID2[i] = :SUBPARENTID[r[1]], :SUBPARENTID2[i] = 9999 ); ); Here is the log file: Snums = Open Database( "odbc connection string", "SELECT [dbo].[BOM].[PARENT] as PARENTID ,[dbo].[BOM].[COMPONENT] as SUBPARENTID FROM [dbo].[BOM] --as tb1 --LEFT JOIN [dbo].[BOM] ON [dbo].[BOM].[PARENT] = [dbo].[BOM].[COMPONENT]" ); //Creates the new column Snums << New Column( "SUBPARENTID2", Numeric ); /*Loops SUBPARENTID to check all rows for matches against PARENTID. Where a match is found the value in SUBPARENTID is assigned to SUBPARENT2ID at the row containing the value used to initiate the match adds 9999*/ For( i = 1, i <= N Rows( Snums ), i++, r = Snums << Get Rows Where( :PARENTID == :SUBPARENTID[i] ); If( N Rows( r ) == 0, Continue(), N Rows( r ) == 1 & r[1] == i, :SUBPARENTID2[i] = 0, N Rows( r ) == 1, :SUBPARENTID2[i] = :SUBPARENTID[r[1]], :SUBPARENTID2[i] = 9999 ); ); /*: Column "SUBPARENTID2" requires numeric values in access or evaluation of 'Bad Argument' Rows in table check for default filter: 1561136 Rows in table check for default filter: 1561136
... View more
