Multi-Row Formula Query

[DHB]

I have some data that includes the first  4 columns in the table below and would like to generate the fifth column using a Multi-Row Formula;

Activity Number	Tile_Num	Tile_SequenceNum	Day	Activity Number per Tile
1	1	1	Monday	1
2	1	2	Tuesday	2
3	1	3	Wednesday	3
4	1	4	Thursday	4
5	1	5	Friday	5
	1	6	Saturday
	1	7	Sunday
6	2	1	Monday	1
7	2	2	Tuesday	2
8	2	3	Wednesday	3
9	2	4	Thursday	4
10	2	5	Friday	5
	2	6	Saturday
	2	7	Sunday

Basically the formula would count  the number of non-null  'Activity Number(s)'  per Tile. 

I've been unsuccessful so far and if anyone could offer some advice I'd be very grateful.

Thank you in advance for your help.

DHB.

[BenMoss · Accepted solution]

To me it looks like you are simply looking bring through the tile_sequencenum when the activity number is not blank as the values are exactly the same. If not can you post some more realistic sample data to the problem you face.

If the answer is yes then you can simply use a standard formula tool with the syntax 

if [Activity Number] = "" then "" else [Tile_SequenceNum] endif

If you want something like a 'grouped by' record ID you can use the following syntax within the Multi-Row Formula tool.

if [Activity Number] = "" then "" else [Row-1: New Field] +1 endif

Ensuring you have grouped by the 'Tile_Num' field and your data is sorted so the blank values in the activity number which we don't want a value to appear in would be at the bottom of the list when it is streamed into the Multi-Row formula tool.

Example attached.

Ben

[DHB]

Thanks so much Ben,

I had locked in myself in to using the Multi-Row Formula tool but your reply made me realise there was a simpler way.  I ended up using a simple formula creating a new column called Tile_Activity_Count;

Tile_Activity_Count = if isnull([Activity Number]) then Null() else [Tile_SequenceNum] endif

[DHB]

I just realised that this doesn't quite work because the sample data that I gave before didn't have any activity within a tile following a null in the same tile.  This complexity is creating a bit of a problem.

I'd like to create the fourth column, 'Tile_Activity_Count'.

Activity Number	Tile_Num	Tile_SequenceNum	Tile_Activity_Count
1	1	1	1
2	1	2	2
3	1	3	3
4	1	4	4
5	1	5	5
	1	6
	1	7
6	2	1	1
7	2	2	2
8	2	3	3
9	2	4	4
10	2	5	5
	2	6
	2	7
11	3	1	1
12	3	2	2
13	3	3	3
14	3	4	4
15	3	5	5
16	3	6	6
	3	7
17	4	1	1
18	4	2	2
19	4	3	3
20	4	4	4
	4	5
21	4	6	5
	4	7
22	5	1	1
	5	2
23	5	3	2
24	5	4	3
25	5	5	4
26	5	6	5
	5	7

I run into problems at Tile 4 Tile_SequenceNum 6 which which occurs after a null Activity_Number at Tile 4 Tile_SequenceNum 5.  My formula gives a 6 (the Tile_SequenceNum) but it needs to be a 5 i.e. the 5th activity within the tile.

Does anyone have any suggestions?

[DHB]

I've managed to solve it by filtering out all the non-null Activity_Number rows and using Ben's Multi-Row tool (formula below) just on those before Unioning the Null rows back in and sorting them back into the right order.

if !isnull([Activity Number]) then [Row-1:Activity Number per Tile]+1 else Null() endif

[alyon]

Hi There! I would like that if there are 4 Values for the same record ID or project that are 0, then anything after should be 0 as well. Any idea on how I can accomplish this? I’ve been trying to setup the multi-row formula for this but I keep getting errors on my formula syntax. (See attachment)