How to make cross join?

CloverDX Platform
1

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

2

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.

3

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.

4

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

5

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.