How to make cross join?

lida

• July 05, 2012 00:00

• Answered

Follow

Hi, I have a trouble joining 2 data files.
First table looks like this:
A date1 10
B date1 12
B date2 5
C date2 25

and second one is like this:
date1
date2

I'm trying to join them into one table:
A date1 10
A date2 0
B date1 12
B date2 5
C date1 0
C date2 25

So I need to put "0" where if there is no value in table 1 from one of the dates from table 2

• 

  dpavlis

  • July 06, 2012 07:01

  0

  Comment actions Permalink

  It looks like HashJoin set to left-outer join would be the solution.
  
  Put your second data set on second/slave port and set the join type to Left-outer. The key in this situation are your respective field carrying the date values.
  Then in the transformation function, you will have to check for each pair for the null value in date field on second port - that means match between master(first) set and second was not found.
  
  David.
  
  PS: You may have a look at this video tutorial - section joining data.
• 

  lida

  • July 06, 2012 18:06

  0

  Comment actions Permalink

  Thank you, David for prompt reply.
  May be I didn't describe the problem clearly.
  The problem is that in first table there is not always a row with the value from the second table. Let me simplify my example:
  
  Table one:
  A date1
  B date2
  
  Table two:
  date1
  date2
  
  Result I'm trying to get:
  A date1
  A date2
  B date1
  B date2
  
  Thank you for your help.
• 

  admin

  • July 09, 2012 12:45

  0

  Comment actions Permalink

  Hi lida,
  
  I am afraid it cannot be done directly in CloverETL. There is issue in our Jira - https://bug.javlin.eu/browse/CL-83 (sorry, it is partially in czech) - to create implement cross join component.
  
  Workarounds:
  
  1] use database (DBInputTable component can be filled by join)
  
  2] constant artificial id and join component - take a look on attached example

  • test_crossjoin.grf
• 

  lida

  • July 09, 2012 22:06

  0

  Comment actions Permalink

  I found a solution for my problem.
  I splited table1 into to tables( one table with date1, second with date 2), then added one empty column to one of the tables an then merged them.

Please sign in to leave a comment.