VLOOKUP Function with IF Condition in Alteryx

[Vikas27]

Hi Friends

I'm newbie to Alteryx. I'm struck to a VLOOPUP Function in Alteryx. I tried using Find & Replace and Join Function but it didn't worked. So, to start with I have 2 data sets which I want to marry up to get the new data in new tab created in file 1.

In excel i can simply use the condition IF(Strategy=",", VLOOKUP(Ticker, File 2!A:B,2,FALSE),Strategy)). 

But how to use the same feature in Alteryx. I tried marrying both files with Find & Replace tool i was able to amend Ticker data in File 1 and replace with Strategy data of File 2. But faced 2 problems -

1) Where i see blank in excel my IF condition pick strategy from File 1. 

2) I was not able to create new column in File 1 to apply the VLOOKUP. It was applied to existing columns. 

File 1 

Ticker	Strategy
PNL TICKER: PNL ENTITY: Collateral Funding	Collateral Funding
PNL Ticker: Reserves
PNL Ticker: Brokerage
	Options

File 2

Ticker	Strategy
NOTAPPLICABLE	VMASTER
CDXHY2206/19V4	HY22
CDXHY2312/19V3	HY23
NOTAPPLICABLE	VMASTER
CDXHY2206/19V4	HY22

Can someone please help me with the flow? 

Best Regards

Vikas

[AlexSTeryx]

Hi @Vikas27 ,
I'm not sure I got what you want. I rebuild first your example in Excel and there are no matches between File 1 and File 2:

Sorry fro the German Parameters in the formula (SVERWEIS = VLOOKUP, WENN = IF)
I'll add one data set, that creates a match and try to build a WF in Alteryx.
C U 
Alex

[AlexSTeryx · Accepted solution]

Hi @Vikas27 , 
I added a line to File 1

In the attached WF I come to the same result:

Is it that you were looking for?
Best
Alex

[Vikas27]

Hi Alex -

Sorry, I gave bad data for Comparison. So partially yeah, so what i want is - 

A) File 1 has ticker CDXHY2206/19V4 we have same in File 2 under Column header Ticker it should match and give Strategy as HY22 like you mentioned. 

B) For Cases like PNL Ticker: Reserves in File 1, we have for same ticker Strategy mentioned as Reserves in file not captured in Sample data. It should give me as Reserves. 

C) For the blanks in File 1 in Ticker tab like in Row wherever after VLOOKUP it see blank or Null with the IF Condition it should get the Strategy from File 1 like Options in this case. 

And all these i need in new Column in my existing data set in File 1. 

In nutshell if Ticker matches in both files give me Strategy details from File 2. If not, give me Strategy as mentioned in file 1. 

[Vikas27]

@AlexSTeryx - Also, how can we get VLOOKUP on new column under same data set. I was not able to get thru Find and Replace feature. 

[AlexSTeryx]

hi @Vikas27, 

I apologise, but I've difficulties to follow you: 

I do not understand what you mean in B) and C).

As for B) would you mind setting up an example in Excel as I did above?

Regarding C) I understand in cases where is a match in File 2 you want to get back "Strategy" from File 2, otherwise you will pick "Strategy" from File 1 and if this is empty / null the you want to see "options"? Is that correct?

Besides of that to my understanding the provided WF does exactly do what you described "in a nutshell". But obviously I got something wrong, since you are not yet happy with the solution.

Would you mind explaining again?

Best

Alex

[binuacs]

@Vikas27 @It would be very helpful if you provide a sample test file with expected result 

[AlexSTeryx · Accepted solution]

Hi @Vikas27, 

in the workflow you can see how I attached the data to your original data set.

Cheers

Alex

[Vikas27]

@AlexSTeryx - I tried using your WF i was close to result but not 100%. So, i've attached excel files for easiness 

Input file has multiple Columns. I want Workflow to create Column F (Strategy Updated) and do a VLOOKUP basis Ticker and pick Strategy from Mapping tab wherever possible. Where there is no mapping like in example of Options in Input file Strategy is blank in that case Input file will not be able to locate anything from Mapping file. In that case it can give me Options as result from same Input file Strategy tab. 

Sorry for the confusion earlier, hope this simplifies. 

@binuacs - Please share your expertise. 

[AlexSTeryx]

Hi@Vikas27 
currently I’ve only my mobile , so I cannot download the files and build a workflow.

As far as I do see from the files there is no case where Strategie is empty in input file and where is also no match in mapping file. Do you have such cases and if soo, what do you want to have in then column F : blank, [Null] or something else?

to write „Option“ in column F you‘ve only amend the formula in the WF so that it picks „Option“ from column B if „Strategy“ is „Option“.

I‘ll no access rot my Alteryx before Monday.

best

Alex