I have a 2-D array indexed by Group and Event. Each Group has a different number of Events, so each Group-Slice is null-padded. From this I want to compute a 1-D array, where the values for all events are listed but with a single Null cell between each Group's values. In other words, the values for all of Group 1's events appear, followed by Null, followed by the value for all of group 2's events, followed by a Null, followed by the value for all of group 3's events, etc.
I actually came up with some code to compute this, but it is pretty ugly. The starting array is named Attendees. My solution looks like this:
Local temp := ConcatRows(Attendees, Group, Event);
temp[.ConcatIndex=subset(temp<>null or temp[.ConcatIndex=temp.ConcatIndex-1]<>null,ResultIndex:Event_num)]
The first line changes it from a 2-D to 1-D, but with all the original nulls present. The second line uses subset to decide which of those cells to keep.
Is there a more elegant way of doing this transformation?
I've attached a simple model to show the starting array and my solution. I'm looking for a replacement for my expression in Flattened_attendees.
Sorry, I forgot to attach my example model to my previous post. Here it is.
Jessie - this is a timely example for me, since I actually just starting to learn about array functions! I studied your solution and get it. Seems pretty good to me, although I am still going to see if I can come up with my own solution. However, I think I found a problem with your solution. See what you think...
In your Attendees table, I added data to every cell in Group 2 (so there are 20 events with data, and no null-padding at the end). When you do that, there is no Null value between your Group 2 and Group 3 in the Flattened attendees result. I guess if you never fill every cell in a group you'll be okay.
I came up with a different solution. I think it isn't as elegant as yours, but it does fix the bug I mentioned in my previous post with a Null after "full" group. Here is what I came up with:
Local result[Event_num]; ( Local firstForGroup[] := Cumulate(Num_events_with_atte+1,Group) [Group=Group-1,defVal:0]+1; Local numEvents[] := Num_events_with_atte; Local valsForGroup[Event] := Attendees; For i := 1..numEvents Do result[Event_num=firstForGroup+i-1] := valsForGroup[Event=i]; ); result
firstForGroup is the position where each group starts in the result array.
I know that more elegant solutions would do this with array operations and without resorting to using FOR and Assignment, like your original expression. I'm going to keep trying, but hope that someone shows their solution.
@jessiesandoval - Your original definition (with the bug Pat found) is equivalent to:
Flatten(Attendees, Group, Event, ResultIndex:Event_num, condition:@Event<=Num_events_with_atte+1)
To address the case where there is no trailing null event, I think I would reindex the same way you did in your update, i.e.,
LocalIndex E2 := Concat(Event,'extra'); Flatten(Attendees[@Event=@E2], Group, E2, ResultIndex:Event_num, condition:@E2<=Num_events_with_atte+1);
If you encapsulate this as a User-Defined function, where Event is an index parameter, then use a local index like this so it adjusts to any index length. (You would also have to compute Num_events_with_atte in a local inside the UDF). If you are only doing this for the global event index, then using a global index, Event_with_extra, would make your expression simpler, and avoid the (fairly small) overhead of creating a local index.