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!
);
);
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" )
);
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 |
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
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 |
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.
