Alteryx Designer Desktop Discussions

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

Multifield Formula assistance

TC101
5 - Atom

I am trying to build a workflow that will, among other things, look at a series of payments to clients and flags payments to clients for additional checks based on a specified criteria. I have managed to do this using formula but it is not a practical solution given the size of the data set. I have created the following data set based on the one I am working with:

 

Client ID

Jan_Pymt

Jan_Qty

Feb_Pymt

Feb_Qty

Mar_Pymt

Mar_Qty

CID01

25

5

5

0

25

6

CID02

25

5

20

1

15

2

CID03

50

9

50

5

10

0

CID04

0

2

20

0

5

0

CID05

25

5

5

5

5

5

CID06

25

0

10

8

15

10

CID07

25

4

10

6

10

10

CID08

25

0

0

1

20

10

CID09

25

3

50

4

20

0

CID10

50

2

20

0

25

5

 

Is it possible to use a Multifield Formula that will:

  1. Add a new field that calculates the sum of the "Pymt" columns based on the value in the "Qty" columns. For example, sum the payments when the quantity = 0 (or if possible based on a ratio of the payment to quantity being less than a specified value).

  2. Adds a new field that adds a flag "Check" based on the same criteria used for summing the payments.

The goal is being able to filter clients for checking and being able to use the value of the sum of payments to prioritise the order of the Clients for payment checks to be made.

3 REPLIES 3
Luke_C
17 - Castor
17 - Castor

Hi @TC101 

 

With this dataset what would your expected result look like for your criteria? I don't think multi-field is the way to go. I'd suggest transposing the data, doing any filtering/logic, and summarizing back up by client ID.

TC101
5 - Atom

I was hoping for the output to look something like this:

 

Client IDJan_PymtJan_QtyFeb_PymtFeb_QtyMar_PymtMar_QtySum of PaymentsCheck
CID01255502565Yes
CID02255201152 No
CID0350950510010Yes
CID04022005025Yes
CID052555555 No
CID06250108151025Yes
CID072541061010 No
CID0825001201025Yes
CID0925350420020Yes
CID1050220025520Yes

 

Then we can filter and sort the out put based on the two additional new columns. I will give your suggestion a go.

Luke_C
17 - Castor
17 - Castor

Hi @TC101 

 

Here's what I was envisioning:

 

  • Transpose by client ID
  • Split the headers by underscore
  • Cross tab back to have client ID - Month - Payment - Qty
  • Filter to 0 Qty and sum payment
  • Join back to dataset and add check

image.png

Labels
Top Solution Authors