Re: Count values in two columns

Fabian_Weller · 11-30-2021 08:58 AM

I have 2 enum columns in the same row. Both can have the value “A” or “B” or “C”.
In a VC I want to count how many times the value is “A”.

I have tried different expressions. They are all working. I also measured the sync time. But the results in the Performance Analyzer are not quite accurate, so I don’t really know which one is the fastest.
Maybe someone has insight on that?
Does anyone know a better solution?

Version 1

IFS(
	AND([Column1]="A",[Column2]="A"),2,
	OR([Column1]="A",[Column2]="A"),1
)

Version 2
IFS([Column1]="A",1)+IFS([Column2]="A",1)

Version 3

COUNT(LIST([Column1]="A")-LIST(FALSE))
+
COUNT(LIST([Column2]="A")-LIST(FALSE))

Version 4
COUNT(LIST([Column1]="A",[Column2]="A")-LIST(FALSE))
This one is not working if both columns have the value “A”, because -LIST() has the side effect of deleting duplicates. So in this case LIST(TRUE , TRUE) will get just LIST(TRUE).

Marc_Dillon

I’d go with Version 2.

Marc_Dillon

Or, instead of simple Enum columns, make them Enum-Ref, pointing to a simple Table where 1=A, 0=B. Then just add the values.

[col1].[val] + [col2].[val]

I use a “code_table” setup in the vast majority of apps that I build, for holding Enum options, and translation values and etc.

Fabian_Weller

OK thank you. I think this is not so good for my case. Because in two other VCs I also want to count how often the value is “B” or “C”.

Wesley

If speed is your concern, I’d steer clear of the VC completely and just add it as a formula for a real column. As long as column 1 and 2 aren’t references pr other VCs you should be fine (real column will be updated automatically only on change/save instead of every sync)

I feel this change would have the greatest long term impact.

Suvrutt_Gurjar

Hi @Fabian ,

The requirement you are having is centered around single row formulas in VCs. This means even though there may be multiple records in a table, each VC expression is computed for that particular row only as against multi-record processing required in SELECT() and associated family ( MAXROW(), MINROW() etc. ) based expressions that are typically likely to put a drag on sync performance.

May we know any specific reason you are emphasizing on knowing the performance for such single row VC expressions?

I did some testing on a large table and can share my observations, but just wanted to be sure, that my understanding of your requirement is correct and that it is around sync performance.

Fabian_Weller

Hi @Suvrutt_Gurjar
My sheet has 45 columns and 20.000 rows = 900.000 cells.
I want to save every sync second
In my testing, this is the sync time:
Version 1: 0,5 sec
Version 2: 0,3 sec
Version 3: 0,2 sec
Version 4: 0,2 sec
But it’s not very accurate, so it differs from sync to sync.

I know I could change the VCs to “real” columns. But the more real columns, the longer the loading time for the sheet, isn’t it?
So I would like to stay with VCs but with the lowest sync time.

Suvrutt_Gurjar

Oh got it. Now your scenario is clear. Thank you very much @Fabian

Sharing some more observations. I tested ut on a 37,784 row table with 22 columns. Version 1, Version 2, and Version 3 expressions are exactly same expressions shared by you and also tested on enums as shared by you. Hope this helps.

In this test, ( of course it cannot be generalized), the single row expressions took roughly 4 % to 6 % time of complex multirow SELECT() expression.
Edit: Your observation is correct that it differs from sync to sync. However, Version 2 by far appears the most efficient.

Hope this helps.

Fabian_Weller

Wow thank you so much for your work @Suvrutt_Gurjar !!!

Suvrutt_Gurjar

You are welcome @Fabian and thank you to you as well. I was aware that if @Fabian is posting some requirements, it has deep insights. You brought out an important topic. Had previously also tested on similar lines for multirow expressions as below.