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

Help with Multi Row tool to add down

HW1
9 - Comet

Hi,

I want the multi row formula to add numbers when there is non null cell.

 

How can I do it?

 

I have the values currently as:

 

RecordIDAddress
1 
2 
31
4 
5 
6 
7 
8 
9 
101
11 
12 
13 
14 
15 
16 
17 
18 

 

I want them to be 

RecordIDAddress
1 
2 
31
42
53
64
7 
8 
9 
101
112
123
134
14 
15 
16 
17 
18 

 

I am using the multi row tool to be :

 

If !isNull([Address])
THEN [Row+1:Address] +1
ELSE [Address]
ENDIF

 

But its not cutting it..

what should the multirow formula be?

 

Please find attached workflow for reference

4 REPLIES 4
HW1
9 - Comet

Figured out a solution in case anybody needs it.

 

 

AngelosPachis
16 - Nebula

Hi @HW1 ,

 

Can you try :

 

If !isNull([Address])
THEN [Address] 
ELSE  [Row-1:Address] +1
ENDIF

 

and make sure that you configure the multi-row formula tool to treat not available values as 0

 

I think this should do it. Is there any reason the address goes all the way until 4?

 

Thanks,


Angelos

HW1
9 - Comet

@AngelosPachis  Yep. I am parsing PDF invoices which are hundreds and the addresses are split into different rows with underlying identifiers.

I want to concatenate the values so that the address is output in a single row. I am trying to figure that out.. If you can help, that can be great!

 

The progress so far:

 

RecordIDValueAddress
1Flick Anticimex Pty Ltd TAX INVOICE 
2Page 1/2 
3😎 Anti Ci mex’ Flick Anticimex Pty Ltd ABN 85000059665 
4Your Bill-To Number Date of Issue Invoice Number Bill Enquiries 
6Email 
7. Total Payable 
8Sandra & John Wright Pty Ltd y 
9Accounts Payable Atn: Gaby 16.68 
10PO Box 118 
11ABN 13168617178 Due Date 
12Seven Hills, NSW 1730 07/04/21 
13Reference Date No. Unit Price Total Price 
14For the following services provided P/O No No. Completed Units (Ex GST) (Ex GST) 
15Monthly In Advance Invoice from Date of 
16Issue 
17Waste Options- Lavinia Horne1
18Unit 5, 32 Export Dr Whakatu Gold Coast2
19Fire Protec, Molendinar3
21Sanitary Service 
23Location Subtotal: 7.58 
24Monthly In Advance Invoice from Date of 
25Issue 
26Waste Options - Jaana Brown1
2798 Brisbane Terrace Sip ‘n’ Dip Australia,2
28Goodna3
30Sanitary Service 
32Location Subtotal: 7.58 
33Flick Anticimex Pty Ltd TAX INVOICE 
34Page 2/2 
35😎 Anti Ci mex’ Flick Anticimex Pty Ltd ABN 85000059665 
36Your Bill-To Number Date of Issue Invoice Number Bill Enquiries 
38Email 
39. Total Payable 
40Sandra & John Wright Pty Ltd y 
41Accounts Payable Atn: Gaby 16.68 
42PO Box 118 
43ABN 13168617178 Due Date 
44Seven Hills, NSW 1730 07/04/21 
45Reference Date No. Unit Price Total Price 
46For the following services provided P/O No No. Completed Units (Ex GST) (Ex GST) 
47Total Ex-GST: 15.16 
48Total GST Amount: 1.52 
49Total Payable: 16.68 
50GENERAL INQUIRIES: National.Enquiries@flick.com.au. 
51Credit Claims Must be advised in writing within 14 days of the invoice date 
52Your Bill-To Number Invoice Number Bill Enquiries Due Date Total Amount Payable 
54a Remittance Details for EFT Payments : 
56Ref: 1000002095 Please quote invoice number for prompt allocation 
57Remittances can be emailed to: 
58Telephone & Internet Banking — BPay® accounts.receivable@flick.com.au 
59Contact your bank or financial institution to make this payment 
60from your cheque, savings, debit, credit card or transaction 
61account. More info: www.bpay.com.au OVvisa [J Mastercard Tamex 
62Flick Anticimex Pty Ltd Amount Expiry Date cvc 
63Homebush West, NSW 2140 
64Card Holder's Name 
65Customer Signature 

 

 

Expected:

 

RecordIDValueAddressAddress_1
1Flick Anticimex Pty Ltd TAX INVOICE  
2Page 1/2  
3😎 Anti Ci mex’ Flick Anticimex Pty Ltd ABN 85000059665  
4Your Bill-To Number Date of Issue Invoice Number Bill Enquiries  
6Email  
7. Total Payable  
8Sandra & John Wright Pty Ltd y  
9Accounts Payable Atn: Gaby 16.68  
10PO Box 118  
11ABN 13168617178 Due Date  
12Seven Hills, NSW 1730 07/04/21  
13Reference Date No. Unit Price Total Price  
14For the following services provided P/O No No. Completed Units (Ex GST) (Ex GST)  
15Monthly In Advance Invoice from Date of  
16Issue  
17Waste Options- Lavinia Horne1Waste Options- Lavinia Horne Unit 5, 32 Export Dr Whakatu Gold Coast Fire Protec, Molendinar
18Unit 5, 32 Export Dr Whakatu Gold Coast2 
19Fire Protec, Molendinar3 
21Sanitary Service  
23Location Subtotal: 7.58  
24Monthly In Advance Invoice from Date of  
25Issue  
26Waste Options - Jaana Brown1Waste Options - Jaana Brown 98 Brisbane Terrace Sip ‘n’ Dip Australia, Goodna
2798 Brisbane Terrace Sip ‘n’ Dip Australia,2 
28Goodna3 
30Sanitary Service  
32Location Subtotal: 7.58  
33Flick Anticimex Pty Ltd TAX INVOICE  
34Page 2/2  
35😎 Anti Ci mex’ Flick Anticimex Pty Ltd ABN 85000059665  
36Your Bill-To Number Date of Issue Invoice Number Bill Enquiries  
38Email  
39. Total Payable  
40Sandra & John Wright Pty Ltd y  
41Accounts Payable Atn: Gaby 16.68  
42PO Box 118  
43ABN 13168617178 Due Date  
44Seven Hills, NSW 1730 07/04/21  
45Reference Date No. Unit Price Total Price  
46For the following services provided P/O No No. Completed Units (Ex GST) (Ex GST)  
47Total Ex-GST: 15.16  
48Total GST Amount: 1.52  
49Total Payable: 16.68  
50GENERAL INQUIRIES: National.Enquiries@flick.com.au.  
51Credit Claims Must be advised in writing within 14 days of the invoice date  
52Your Bill-To Number Invoice Number Bill Enquiries Due Date Total Amount Payable  
54a Remittance Details for EFT Payments :  
56Ref: 1000002095 Please quote invoice number for prompt allocation  
57Remittances can be emailed to:  
58Telephone & Internet Banking — BPay® accounts.receivable@flick.com.au  
59Contact your bank or financial institution to make this payment  
60from your cheque, savings, debit, credit card or transaction  
61account. More info: www.bpay.com.au OVvisa [J Mastercard Tamex  
62Flick Anticimex Pty Ltd Amount Expiry Date cvc  
63Homebush West, NSW 2140  
64Card Holder's Name  
65Customer Signature  

 

I guess the multi row formula tool can help. How can I do it?

AngelosPachis
16 - Nebula

@HW1 In this instance you need a summarize tool to concatenate rows that have 1,2,3 in the address. To do that, you must first use a multi-row formula tool to group rows you want to concatenate together, so you can use that new field to group on later on.

 

AngelosPachis_0-1620105897880.png

 

Hope that helps,

 

Angelos

 

Labels