Looping query help!

Question

Hello all,

I am struggling with a query and need your help!

I am trying to turn this:

ParentComponent121314155667

Into this:

ParentComponent121314151617

Here is the code that i have thus far, but i can not get it to work.

Snums = Open Database(

"DSN="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]"

);

Snums << New Column( "SUBPARENTID2", Character );

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] = : SUBPARENTID2[i]

););

I am trying to learn JMP code as fast as possible (new job) but am under a tight deadline with this one. Please help!

);

uday_guntupalli · Answer

@Minutemaid23, 
 Here this works for the example you provided. I think this largely works and the only complication I can see is if the original list of parent and component had a many to many relation instead of one to one.

Clear Log(); Clear Globals();

// Get Reference for data table
dt = Current Data Table();

// Extract Values from Parent and Component and wrap them in associative arrays
ParentVals = Associative Array(dt:Parent << Get Values) ; 
ComponentVals = Associative Array(dt:Component << Get Values);

// Find Common elements between Parents and Components 
Intersection = ParentVals; 
Intersection << Intersect(ComponentVals); 
Common = Intersection << Get Keys;

// Update Data Table 
dt << Begin Data Update;

for(i = 1, i <= N Items(Common), i++,
		SR1 = dt << Select Where(:Parent == Common[i]) << Get Selected Rows; 
		dt << Clear Select; 
		SR2 = dt << Select Where(:Component == Common[i]) << Get Selected Rows; 
		NewParent = dt:Parent[SR2]; 
		dt << Clear Select; 
		dt << Add Rows(1); 
		nR = nRows(dt); 
		dt:Parent[nR] = NewParent; 
		dt:Component[nR] = Common[i];
   );

dt << End Data Update;

// Remove Duplicates
dt_res = dt << Summary(
							Group( :Parent, :Component ),
							Freq( "None" ),
							Weight( "None" )
					  );

Minutemaid23 · Answer

The best description that I can provide is below. Since 5 is a component under 1, and 6 is a component of 5, then 5 and 6 are under 1.

It turns this:

ParentComponent121314155667

into this:

ParentComponent121314151617

uday_guntupalli · Answer

@Minutemaid23,

From what I understood, here is where I am going

Clear Log(); Clear Globals();

// Get Reference for data table
dt = Current Data Table();

// Extract Values from Parent and Component and wrap them in associative arrays
ParentVals = Associative Array(dt:Parent << Get Values) ; 
ComponentVals = Associative Array(dt:Component << Get Values);

// Find Common elements between Parents and Components 
Intersection = ParentVals; 
Intersection << Intersect(ComponentVals); 
Common = Intersection << Get Keys;

The idea I am going by is : 
Step 1 : In order for you to bring these elements over, you will first need to identify what the common elements are i.e. what elements exist as both Component and Parent

Step 2: Once you know the common elements, I don't know what you mean by bringin them over, if you could help me understand that a little better - else this should atleast get you started in the right direction