Wildcard in Alteryx (Coming from excel using sumif)

[fordfold]

Hi Trying to move some work from Excel into Alteryx. Basically I have a cross tab that groups data by one field of variable length in one currency. Then I have another set of data in another currency that uses the same field but it has a space then other text within it. In excel I just used a sumif    =SUMIF(A2:A5,G2&"*",B2:B5)  This links the data together and then I can easily find the exchange rate between them  Not not sure how to replicate the sumif with wildcard in Alteryx?

[GiuseppeC]

Hi @fordfold, 

can you post some sample data and desired results?

Giuseppe

[JordyMicheal]

Can you possibly post a sample of data?

It's most likely IF ELSE logic you are looking for.

IF case

THEN action

ELSE action

ENDIF

[fordfold]

So basically 2 tables and I want to end up with another column in the first table that sums the matching line from the second.

Table1_ID#                    Amount$           

0903FE6707                 150                    

0903FE6172                 180

5001J2109                    200

Table2_ID#                                    Amount £

0903FE6707      54050450                   55

0903FE6707        9999999                   54

0903FE6707      8888888                     58

0903FE6172      546456                        30

0903FE6172       5478454                    20

0903FE6172         4564654                  50

5001J2109              3452352535          20

5001J2109                2352352353        20

5001J2109            34523455                20            

[JordyMicheal]

You could use a join on the TABLE1_ID# to TABLE2_ID#

This will join any table id's and you well get a TABLE1_ID$ AMOUNT$ TABLE2_ID# AMOUNT

Then just use a formula of [amount$] + [amount]

Make sense?

[GiuseppeC]

Hi @fordfold,

the attached workflow should get you what you want. Join the 2 tables on the common field first and then group by and sum using the Summarize tool.

Hope this helps!

Giuseppe

[fordfold]

But the join wont work because there are no matching records?

[GiuseppeC]

I probably didn't get what you are trying to achieve, then, @fordfold. I do see matching records in your sample data.

Can you please clarify?

[fordfold]

For example in Table1   the value is  "0903FE6707"

But in Table2 the values is "0903FE6707      54050450 "

So the join see different values. I get everything in the same table with a union. But dont know if thats the best way to go about it.

In excel I can use the sumif with a wildcard to sum anything that starts with "0903FE6707" no matter how long the values or spaces in between are.

[GiuseppeC · Accepted solution]

Sorry, I didn't get those 2 strings were part of the same field.

You can achieve the same thing with RegEx, but it's probably an unnecessary complication. I'd suggest you split the field in 2 in the second table and match on the common value with the field in the first table, using the Text to column. 

I have updated my workflow to show it.

Hope this helps,

Giuseppe