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