Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Wildcard in Alteryx (Coming from excel using sumif)

fordfold
6 - Meteoroid
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?
12 REPLIES 12
GiuseppeC
Alteryx
Alteryx

Hi @fordfold

 

can you post some sample data and desired results?

 

Giuseppe

JordyMicheal
11 - Bolide

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
6 - Meteoroid

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
11 - Bolide

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
Alteryx
Alteryx

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.

 

clipboard_image_0.png

 

Hope this helps!

Giuseppe

fordfold
6 - Meteoroid

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

GiuseppeC
Alteryx
Alteryx

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
6 - Meteoroid

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
Alteryx
Alteryx

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. 

 

clipboard_image_0.png

I have updated my workflow to show it.

 

Hope this helps,

Giuseppe

 

Labels