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

[Modiz]

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?

[E_Røse · Accepted solution]

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.

[vinieme12]

As below

=Max(TOTAL <date> binary)

[Modiz]

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"?

[Modiz]

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