8 Replies Latest reply: Feb 12, 2013 8:49 AM by Manideep Hv

Join 3 tables

haagenfels Feb 11, 2013 4:34 PM

Hi,

I have three tables each with a key field (CustomerID) but within those tables there are 2 other fields (Title, Status) that are the same but have different values. How does one combine them so that I do not get the $Syn 1. I need to combine them for reporting purposes but when i do that i have 3 key fields. If I run a left join, then I am unable to view the distinctions between the fields.

I believe there is a simple solution.

Any help is greatly appreciated.

Thank you,

Nicholas

Re: Join 3 tables

Wojciech Parzyszek Feb 12, 2013 3:23 AM (in response to haagenfels)

there are many other fields in Your tables right?

or there are just CustomerID, Title and Status?

if not You can prepare new key CustomerID_Title_Status
Like Show 0 Likes (0)

Actions
Re: Join 3 tables

Mohit Sharma Feb 12, 2013 4:51 AM (in response to haagenfels)
hiiii

see the attached file

join 3 tables.xlsx 8.7 K

join 3 table.qvw 149.5 K
Like Show 0 Likes (0)

Actions
Re: Join 3 tables

Vishwaranjan Kumar Feb 12, 2013 5:03 AM (in response to haagenfels)
LOAD CustomerID,
     title,
     status
FROM
C:\Users\vishwaranjan\Desktop\tablejoin.xlsx
(ooxml, embedded labels, table is Sheet1);

noConcatenate

LOAD CustomerID,
     title,
     status
FROM
C:\Users\vishwaranjan\Desktop\tablejoin.xlsx
(ooxml, embedded labels, table is Sheet2);

noConcatenate

LOAD CustomerID,
     title,
     status
FROM
C:\Users\vishwaranjan\Desktop\tablejoin.xlsx
(ooxml, embedded labels, table is Sheet3);

join three table.qvw 150.5 K

tablejoin.xlsx 8.8 K
Like Show 0 Likes (0)

Actions
- Re: Join 3 tables
  
  Wojciech Parzyszek Feb 12, 2013 4:55 AM (in response to Vishwaranjan Kumar)
  
  why don't You just create simple loop:
  
  for each vSheet in 'Sheet1', 'Sheet2', 'Sheet3'
  
  LOAD CustomerID,
  title,
  status
  FROM
  [C:\Documents and Settings\BOMI\Pulpit\tablejoin.xlsx]
  (ooxml, embedded labels, table is $(vSheet));
  
  next vSheet;
  
  Like Show 0 Likes (0)
  
  Actions
- Re: Join 3 tables
  
  Wojciech Parzyszek Feb 12, 2013 5:02 AM (in response to Vishwaranjan Kumar)
  
  vishwaranjan
  
  by the way...
  concatenate work with resident table?
  
  i've got an issue...
  
  robo:
  LOAD * INLINE [
      Dealer, KEY, Date, WholeSale, Retail
      D1, D101-01-2013, 01-01-2013, 50, 2
      D1, D103-01-2013, 03-01-2013, 10, 5
  
  ];
  
  final:
  NoConcatenate LOAD Dealer,
       KEY,
       Date,
       WholeSale,
       Retail
       resident robo;
  
  all the fields are still keys and those two tables are linked together:(
  could You explain that?
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Join 3 tables
    
    Mohit Sharma Feb 12, 2013 5:17 AM (in response to Wojciech Parzyszek)
    
    hiii
    there an issue of concatenate.
    in this there is all fields are keys using no concatenation thet will be treated as differe..
    but for either writing concatenate or not (automatic) it will concat data due to the same field name..and remove keys .....
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: Join 3 tables
      
      Wojciech Parzyszek Feb 12, 2013 5:28 AM (in response to Mohit Sharma)
      
      i see...
      
      anybody else got complex explanation?
      
      Like Show 0 Likes (0)
      
      Actions
  - Re: Join 3 tables
    
    Manideep Hv Feb 12, 2013 8:49 AM (in response to Wojciech Parzyszek)
    
    Hi,
    Concatenation doesnot bother whether it is a resident load or data loaded from any other source. If there are matching columns in both tables then automatically both tables will concatenate. If we use noconcatenate then synthetic keys occur and thier number is based on the common fields in both tables.
    
    Like Show 0 Likes (0)
    
    Actions

Go to original post