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 NumberTile_NumTile_SequenceNumDayActivity Number per Tile111Monday1212Tuesday2313Wednesday3414Thursday4515Friday5 16Saturday 17Sunday 621Monday1722Tuesday2823Wednesday3924Thursday41025Friday5 26Saturday 27Sunday 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.

Multi-Row Formula Query

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.

Tips and Tricks

Preparation

Accepted answers

BenMoss

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

CountNonNulls.yxmd

All comments

BenMoss

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

Ben

CountNonNulls.yxmd

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?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703