BookmarkSubscribe
Choose Language Hide Translation Bar
Community Trekker

## Looping query help!

Hello all,

I am struggling with a query and need your help!

I am trying to turn this:

 Parent Component 1 2 1 3 1 4 1 5 5 6 6 7

Into this:

 Parent Component 1 2 1 3 1 4 1 5 1 6 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!

);

);

5 REPLIES 5
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
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.

 Parent Component 1 2 1 3 1 4 1 5 5 6 6 7

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
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:

 Parent Component 1 2 1 3 1 4 1 5 5 6 6 7

into this:

 Parent Component 1 2 1 3 1 4 1 5 1 6 1 7
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;
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" )
);``````

Best
Uday