Joining tables in one dataset

Question & Answer
,
1

Hello,
I have 2 fact tables, with the same granularity level, and 1 dim table that needs to be connected to the 2 fact tables.
The problem is, that each fact table has different data in the join fields, so the dim tables will join only to the table I’ll make the connection with.

For example:
Fact table 1-
Date FarmId val1
1.1.23 1 1
1.2.23 2 1
Fact Table 2-
Date FarmId val2
1.3.23 1 1
1.4.23 2 1
Dim table-
FarmId FarmName
1 Edo
2 Eli

How can I get a dataset that looks like this:
Date FarmName val1 val2
1.1.23 Edo 1 1
1.2.23 Eli 1 1
1.3.23 Edo 1 1
1.4.23 Eli 1 1

Thanks in advance,
Mia

2

Hi @miap
did you tried something like
grafik
BR

3

But in case there are similar dates in both fact tables, the two fact tables won’t connect based on the date, only based on the ‘farmId’ won’t it?
The join between the fact tables needs to be on the fields ‘date’ and ‘farmId’.

4

I’m sorry, the example wasn’t good enough,
Here is a better example:

image

*I’ll also mention that the 2 fact tables are from different data sources.

5

Try to join the fact tables with full join on Date and Farm id with in one datasetA.
Use that datasetA in another datasetB and left join on farm id the dim.

6

I’m not familiar with joining between 2 datasets, how does it work?

7

please check

8

Great thank u very much, it’s a good solution.

Another question, now I have 2 different date fields, is there a way to unit them into one date field?

9

just exclude one in the dataset. :wink:

10

Sure, but they are not sharing the same data:

The output of joining these 2 datasets is:
image

And I want to have one date that contains both dates.

11

ah ok.
then create a new field. Something like
date = ifelse( isNull({date fact 1}) , {date fact 2}, {date fact 1})

12

Ok, that will work :slight_smile:

Thank you, u are very helpful

2 Likes