Inner join with condition

[troy_mech]

I have two tables Import Liquidity Buffer MI, LiquidityRevisedPackDesc in Alteryx and want to perform inner join with condition same like SQL query mentioned in below

UPDATE [Import Liquidity Buffer MI] INNER JOIN LiquidityRevisedPackDesc ON [Import Liquidity Buffer MI].[Pack Level Description] = LiquidityRevisedPackDesc.PackLevelDescription SET [Import Liquidity Buffer MI].[Revised Pack] = [LiquidityRevisedPackDesc]![RevisedPack]
WHERE ((([Import Liquidity Buffer MI].[Revised Pack]) Is Null))

Could anyone please advise how to perform the above mentioned SQL query in alteryx.

[grazitti_sapna · Accepted solution]

Hi @troy_mech,

You can have a workflow design as shown in the screenshot attached.

In the join tool you can give the keys as you have given in the query, then in the formula tool you can write the expression as below to set the values. Please select the column to be updated

If isnull([RevisedPack1]) then [RevisedPack2] else [RevisedPack1] endif

The numbers 1 and 2 are just for the reference purpose, here ReversePack1 represents the column coming from Import Liquidity Buffer MI and other one represents the same column coming from LiquidityRevisedPackDesc. You can rename them as per your convenience in the Join tool itself. Once this is done your columns will be updated  and later on you can use the select tool to select only those columns that you want to take to the workflow further.

If you need further assistance I suggest you provide me with a sample data set and I will be happy to help.

I hope it helps

[troy_mech]

Thank you so much, It works perfectly.

I'm constructing another SQL query in alteryx. Would be more helpful if you suggest for the below

SELECT [Table1] LEFT JOIN [Table2] ON Table1.Concat = Table2.Concat
WHERE Table1.Concat <> Table2.Concat

[grazitti_sapna]

Hi @troy_mech,

Can you please share some input data for both the tables and also the desired output. Furthermore, I would appreciate if you open a new thread for this new query.

[grazitti_sapna]

Hi @troy_mech,

The scenario can be easily achieved using Alteryx.

If I have understood your problem then you want the records to be left joined on 1.concat=2.concat and then you only want those records where 1.concat!=2.concat

This can be achieved by using one join tool on keys 1.concat=2.concat and then you can use the L port of the join tool to see the results. I think the L port would give you your desired results. PFA sample workflow

If you provide sample data, I will be able to help you better on the same