Creating an additional column in sink using logic that involves three different columns from three different source tables respectively

Question

What will be the steps and expression for derived column if the logic for populating the column is as below:

The data mapping is using data factory:

select c.ClaimID, pl. Related StoreNumber as StoreNumber from claim c
inner join ClaimToProviderToProviderToLocation ctptptl on ctptptl. ClaimID = c. ClaimID
inner join ProviderToProviderToLocation ptptl on ptptl. ProviderToProviderToLocationID = ctptptl. ProviderToProviderToLocationID
inner join ProviderLocation pl on pl. ProviderLocationID = ptptl. ProviderLocationID
Order by c. ClaimID desc

The data mapping is happening in between Source sql server to Azure Database using azure data factory.

I tried adding a data flow activity where i added three inner joins to get columns involved in the join. Then i added a derived column transformation, to finally add expression for StoreNumber,but i am unable to see columns from the first two tables/streams if i add derived column right after 3rd join.

What error you are facing?

Pratik Lad
– Pratik Lad

2023-09-16 06:32:01 +00:00
Commented Sep 16, 2023 at 6:32 — Pratik Lad
– Pratik Lad, Commented Sep 16, 2023 at 6:32

Rakesh Govindula · Accepted Answer · 2023-09-22 11:30:55Z

0

After the join transformation, if you want to use any column in the next transformation, join will give the columns like below.

enter image description here

You need to give the column name in the derived column, and you can use any column as per your requirement from the column list from the previous join transformation.

enter image description here

Here, for sample I have used two joins and you can see I am able to use column from first source.

answered Sep 22, 2023 at 11:30

Rakesh Govindula

11.9k2 gold badges5 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating an additional column in sink using logic that involves three different columns from three different source tables respectively

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related