If column A is matching with column A in another table .How to replace column A value in first table with another column value(Column B Value) from 2nd table?

[Trifacta_Alumni · Accepted solution]

Hi @Vijayalakshmi Kollur? in order to compare column values in Table A and Table B there should be a Join or a Union between them so that the recipe steps that we write should be able to take columns from both tables into account.

Assuming there is Join or a Union relation between Table A and Table B, the first step to compare values between 2 columns i.e ColA_TableA with ColA_TableB can be achieved by using an IF condition step.

Something like IF( ColA_TableA ==ColA_TableB, 1, 0) creates a new column that flags as 1 or 0 based on a match. Let's call this column Flag.

As a second step, I'll use a transform Edit Column with Formula to able to replace values of a column based on a condition.

In Edit Column transform, I'll put Columns as ColA_TableA. In the Formula box, I'll write IF(Flag =1, ColB_TableB,$col). Here $col is a reference to the column values in ColA_Table A.

Another approach is to skip the first step. We achieve this by writing just an Edit column with Formula step.

In Edit Column transform, I'll put Columns as ColA_TableA.

In the Formula box, I'll write IF(ColA_TableA ==ColA_TableB, ColB_TableB, $col). Here $col is a reference to the column values in ColA_Table A.

Hope this helps.

Best

Vardan

[TrifactaUsers]

Thanks for your replay. But in my case above Ans didn't work.

Table A" Col 1" column that match "Table B Col1" then replace with "Table B Col B " value in Table A col 1.

[Trifacta_Alumni]

Can you perhaps your recipe, fraction of your dataset and a screenshot of the flow highlighting the tables and recipes you are working with?

[TrifactaUsers]

In my case for Eg : if column "Model " is in one file and col "Machine Type/SKU" in another file so if it matches need to replace "Model" column value with 'Machine Model' which is another column from 2nd table.

• If "Machine Type/SKU" does not match "Model" column, leave model data as is.

Please let me know how I can achieve this in Trifacta?

[Trifacta_Alumni]

Hi, both tables Model and Machine Type/SKU should have a common join key like a Part ID or something. You cannot write one recipe step and have it affect two disparate datasets if they are not Joined or Unioned. Let's say, if you are able to join both datasets the response in my first comment should work.

[TrifactaUsers]

hi ,thanks for your replay in my case For Eg : there are 3 columns called model,machine type nd machine model these are from two diff tables and I joined in single dataset .so if model matches with machine type replace model column value with machine model column value.

I tried If condition as you mentioned in 1st response it does not work because will give condition like

IF(Model == {Machine Type/SKU}, 'Machine Model', 'Model') it will display result as either Machine model or model but what I need if it matches then model column should update witch machine model value .for eg

Model Machine Type/SKU Replace model with Machine model

10A8S0D90D 10A8S0D90D ThinkCentre M93p

if it is matches Model column should update as "ThinkCentre M93p"

like this value should come.

please advise me.

[TrifactaUsers]

can we use nested if function in trifacta

[TrifactaUsers]

Hi Vardan ,

Is it possible to connect you via any chat window?

[Trifacta_Alumni]

The only adjustment you must do in the condition IF(Model == {Machine Type/SKU}, 'Machine Model', 'Model') is remove single quotes from Machine Model and Model. A pair of single or double quotes mean String literals and not Column values.

The condition should be IF(Model == {Machine Type/SKU}, Machine Model, Model). Please note that single quotes are removed to reflect column values. Hope this helps.

Best

Vardan