topic Re: set the value for several rows on the same date depending on the column in App Development

set the value for several rows on the same date depending on the column

Modiz — Tue, 21 Feb 2023 15:44:28 GMT

Hi,

I have tables with columns: date, category, quantity, binary. I would like to add a set expression in the fourth column that will depend on the binary column and partitioned after the date column. If there was at least one negative number in the quantity column on a given day, I would like to put a value of 1 in the ANSWER column for each row, otherwise a value of 0.

date	category	quantity	binary	ANSWER
2023-02-20	A	50	0	1
2023-02-20	B	100	0	1
2023-02-20	C	-1	1	1
2023-02-21	A	30	0	0
2023-02-21	B	40	0	0
2023-02-21	C	20	0	0

Can anyone help me?

Re: set the value for several rows on the same date depending on the column

E_Røse — Tue, 21 Feb 2023 16:40:23 GMT

Try

if(aggr(nodistinct min(quantity),date)<0,1,0)

The aggr finds the minimum quantity for each date. If we leave the nodistinct keyword out the aggr function only fills one row per date - with the nodisticnt keyword, the aggregation is calculated for every row.

Please like and mark my answer as a solution, if it resolved your issue.

Re: set the value for several rows on the same date depending on the column

vinieme12 — Wed, 22 Feb 2023 07:14:19 GMT

As below

=Max(TOTAL <date> binary)

Re: set the value for several rows on the same date depending on the column

Modiz — Wed, 22 Feb 2023 10:53:08 GMT

thanks, I don't think I asked the question very well.

What if there is no quantity column in the data, and the binary column would be a string, instead of 0 is 'yes', instead of 1 is 'no', but there can also be an answer of "maybe". How to get column "answer"?

Re: set the value for several rows on the same date depending on the column

Modiz — Wed, 22 Feb 2023 11:06:09 GMT

based on your answer, i already found the answer, thank you very much