382674_en-US

liliconcom

Combining Data from multiple tables

I'm trying to create a new master table by combining data from two tables (or more). As an example, I'm trying to combine Table A & Table B below into Table A-B as the result:

Basically both table A & B have similar column in "start" & "end" but different range for each row. What I'm trying to do is to combine the segments from starting distance 1 to 20 from both table and combine them into table A-B. 

Any idea on how to do this through either scripting or the Tables functions ?

Advanced Statistical ModelingAutomation and ScriptingContent OrganizationMass CustomizationSharing and Communicating ResultsRe: Combining Data from multiple tables

After some thinking, I found a way to do this interactively. Rather than describe it, I've attached a short video I made that discusses the process. The interactive approach has the advantage that it is the same amount of work (which is not much) no matter how many tables you want to combine. In my example, I use 3. The disadvantage is that it requires some non-intuitive sorting; if you make an error in sorting, you'll get incorrect results.

 

Run this script to generate the tables I use in the example:

 

Names Default To Here(1);

dta = new table("A",
          <<ADDROWS></ADDROWS>

Re: Combining Data from multiple tables

@jthi & @brady_brady Thanks for the responses. I'm sure the solutions provided work but I want to digest them first and I'll respond back here if they meet my intent. Appreciate the help! Keep you posted.

Re: Combining Data from multiple tables

EDIT: I re-wrote this to make it much shorter, more efficient and free of the integer-based limitations of my original response. It is also straightforward to adapt the code to handle more than 2 source tables.

////

 

 

You can also tackle this with JSL's version of a table-lookup, using Loc Sorted().

 

Cheers,

Brady

 

Names Default To Here( 1 );

dt1 = New Table( "Table A",
 Add Rows( 4 ),
 Compress File When Saved( 1 ),
 New Column( "Start", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 5, 10, 15] ) ),
 New Column( "End", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [5, 10, 15, 20] ) ),
 New Column( "Name", Character, "Nominal", Set Values( {"A", "B", "C", "D"} ) )
);

dt2 = New Table( "Table B",
 Add Rows( 5 ),
 Compress File When Saved( 1 ),
 New Column( "Start", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 4, 8, 12, 16] ) ),
 New Column( "End", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [4, 8, 12, 16, 20] ) ),
 New Column( "Name", Character, "Nominal", Set Values( {"X1", "X2", "X3", "X4", "X5"} ) )
);

//////////// Begin processing here...
ends = Matrix( Associative Array( Concat( As List( dt1:End << get values ), As List( dt2:End << get values ) ) ) << get keys );
starts = [1] |/ ends[1 :: (N Row( ends ) - 1)];
dt3 = As Table( starts || ends, <<COLUMN names=""></COLUMN>

 

Re: Combining Data from multiple tables

This can give an idea how you could do this with scripting:

 

Names Default To Here(1);

dt1 = New Table("Table A",
 Add Rows(4),
 Compress File When Saved(1),
 New Column("Start",
  Numeric,
  "Continuous",
  Format("Best", 12),
  Set Values([1, 5, 10, 15])
 ),
 New Column("End",
  Numeric,
  "Continuous",
  Format("Best", 12),
  Set Values([5, 10, 15, 20])
 ),
 New Column("Name", Character, "Nominal", Set Values({"A", "B", "C", "D"}))
);

dt2 = New Table("Table B",
 Add Rows(5),
 Compress File When Saved(1),
 New Column("Start",
  Numeric,
  "Continuous",
  Format("Best", 12),
  Set Values([1, 4, 8, 12, 16])
 ),
 New Column("End",
  Numeric,
  "Continuous",
  Format("Best", 12),
  Set Values([4, 8, 12, 16, 20])
 ),
 New Column("Name",
  Character,
  "Nominal",
  Set Values({"X1", "X2", "X3", "X4", "X5"})
 )
);

//create collection table
dt_final = New Table("Table A-B",
 Compress File When Saved(1),
 New Column("Start", Numeric, "Continuous", Format("Best", 12)),
 New Column("End", Numeric, "Continuous", Format("Best", 12)),
 New Column("Name", Character, "Nominal")
);

For Each Row(dt1,
 startVal = Column(dt1, "Start")[Row()];
 endVal = Column(dt1, "End")[Row()];
 nameVal = Column(dt1, "Name")[Row()];
 r_dt2 = dt2 << get rows where(startVal <= :Start <= endVal | startVal <= :End <= endVal);
 For(i = 1, i <= N Items(r_dt2), i++,
  newEnd = Min(Column(dt2, "End")[r_dt2[i]], endVal);
  newName = nameVal || " " ||Column(dt2, "Name")[r_dt2[i]];
  newRow = EvalList({startVal, newEnd, newName});
  dt_final << Add rows({Start = newRow[1], End = newRow[2], Name = newRow[3]});
  startVal = newEnd;
  endVal= Max(Column(dt2, "End")[r_dt2[i]], endVal);
 );
);

Close(dt1, no save);
Close(dt2, no save);