Hey guys, i have gotten some great help out of here, so i hope someone could help again. A common problem here is to remove “Null” or zero from a list. In my case i want it to be counted and exportet to excel.
I have sorted lists of elements, sorted by area and colours (3 different, later up to around 20 possible) and i want sums of all possible colours in the project.
When there is no colour of a certain, the export-list just skipts the field in excel, which swiftes the coloums to a wrong way. There need to be something written in it, to make sure, the export stays correct.
Here are the pictures going with it.
On the left side the “gelb” is missing in the last list, but there need to be something on the right list after sum to say is 0 or empty.
This is what goes and should go to excel.
There are a couple ways you could go about this but I’ll suggest a fairly straight forward one. If you use IndexOf with your FirstItem output (the three available groups) to find the index of those items within the unique keys output (the actual groups within a sublist) you’ll get the index for each key or a -1 if the item doesn’t exist in that particular sublist. You can then use AddItemToFront to add a 0 or blank to the front of each sublist of the summed values. Now you just have to bump up your list of indices by 1 so those -1s become 0s and GetItemAtIndex will now give you the corresponding sum for each group or a 0 (or blank) for the ones that didn’t exist.
Add a zero to each sublist, then use a List.Clean at level 2 and not preserving the indicies before you sum the results.
[0, null] cleansed is [0] which will sum to 0.
[0,1,2,3] cleaned is [0,1,2,3] which will sum to 6 as if the zero wasn’t there.
This is a frequent ‘trick’ to ensure results in generative design.
You’re not getting the right indices because you have your inputs flipped. You need to check the groups for the 3 available categories. That way the groups with missing categories get a -1. This also takes care of the sorting so you can remove that part prior to GroupByKey.
