Joining two dataset with calculated field from S3 datasets

Question & Answer
, , ,
1

Hi
I have two S3 bucket datasets and have a calculated field in them , now I want to join these two datasets , since join dataset is not supported for calculated fields what is the workaround for this? If anyone has done that already can you pls provide a little detail as I am totally new to this

thanks

2

Hi @muhaalvi ,

Are you wanting to join the two datasets with the calculated field as the join condition? If so, that is not allowed (see here).

But any calculated fields that you define on either, or both, datasets will be part of the new, joined dataset. You just can’t use the calculated field as the join condition.

If you do need to join on the calculated field, can you share more details? What is the calculated field doing in each dataset to make it the join condition?

ws

3

Thanks Steven

Actually it’s a key like ‘SiteA-SiteB’ and is the same in both Dataset A and DataSetB . This key is not unique in either of the datasets i,e this key has multiple entries in both datasets. What I want is that while joining the datasets, this key is searched from dataset B in dataset A and it returns all the entries found in dataset A against the key ‘SiteA-SiteB’

4

Hi @muhaalvi ,

How is this different from a right join? Could you join on the site field from both datasets and then filter for the desired SiteA-SiteB rows after the join?

It will help if you have a small example dataset that shows the keys and relevant fields in the individual datasets, the desired result and the calculated field. It will give me have a more accurate mental model.

ws

5

Hi @muhaalvi,

Did @wstevens01’s reply help find a solution? If so, please mark their reply as solution. If we do not hear back in the next 3 days, we will archive the question.

Thanks,
Andrew