BookmarkSubscribe
Choose Language Hide Translation Bar
Minutemaid23
Community Trekker

Looping query help!

Hello all,

 

I am struggling with a query and need your help!

 

I am trying to turn this:

 

ParentComponent
12
13
14
15
56
6

7

 

Into this:

ParentComponent
12
13
14
15
16
1

7

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!

 

 

 

);

);

 

 

 

0 Kudos
5 REPLIES 5
uday_guntupalli
Community Trekker

Re: Looping query help!

@Minutemaid23,
     Can you help me understand what you are trying to do ? I am unable to follow what table 1 and table 2 are. It will help the community in general and yourself if the question was clearer - so people can try and help you. 

 

 

Best
Uday
0 Kudos
Minutemaid23
Community Trekker

Re: Looping query help!

I will do my best.

 

I am connecting via ODBC to a table that has two columns, Parent and Component. In the table, it is possible for a component to also be in the parent column. For example, the number 5 is a component and a parent. I am trying to write code that looks if a component is in the parent column. If so, i want it to bring back the that component value (6 in this example). Then i am trying for it to check if 6 is in the parent table, and so on.

 

ParentComponent
12
13
14
15
56
67

 

 

0 Kudos
uday_guntupalli
Community Trekker

Re: Looping query help!

@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 

Best
Uday
0 Kudos
Minutemaid23
Community Trekker

Re: Looping query help!

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:

 

ParentComponent
12
13
14
15
56
67

 

into this:

 

ParentComponent
12
13
14
15
16
17
0 Kudos
uday_guntupalli
Community Trekker

Re: Looping query help!

@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" )
					  );

image.png

Best
Uday
0 Kudos