Tag time slots used

[stha142020]

Hi,

I have a table for available slot times in different stores. If a slot is booked and also cancelled, it is created a new row in the table with the same time slot for the same store. I do not however have a field that says if the slot is used or not.

This is my desired result:

Store number	Date	SlotStartTime	SlotEndTime	BookingFlag	CancelledFlag	Slot used
1	03.09.2020	12.00	13.30	1	1	0
1	03.09.2020	12.00	13.30	1	1	0
1	03.09.2020	12.00	13.30	0	0	0
2	03.09.2020	12.00	13.30	1	1	1
2	03.09.2020	12.00	13.30	1	0	1
3	03.09.2020	12.00	13.30	1	0	1

Any suggestions?

Thanks in advance!

[Taoufiq_Zarra]

@stha142020  one possibility

Table:

LOAD *,rowno() as Idtmp,autonumber([Store number]&Date&SlotStartTime&SlotEndTime) as FlagTmp INLINE [
    Store number, Date, SlotStartTime, SlotEndTime, BookingFlag, CancelledFlag
    1, 03.09.2020, 12.00, 13.30, 1, 1
    1, 03.09.2020, 12.00, 13.30, 1, 1
    1, 03.09.2020, 12.00, 13.30, 0, 0
    2, 03.09.2020, 12.00, 13.30, 1, 1
    2, 03.09.2020, 12.00, 13.30, 1, 0
    3, 03.09.2020, 12.00, 13.30, 1, 0
];
left join
load FlagTmp,FirstSortedValue(BookingFlag,-Idtmp) as BookingFlagTmp,FirstSortedValue(CancelledFlag,-Idtmp) as CancelledFlagTmp  resident Table group by FlagTmp;

Final:

load [Store number], Date, SlotStartTime, SlotEndTime, BookingFlag, CancelledFlag, if(BookingFlagTmp=1 or CancelledFlagTmp=1,1,0) as [Slot used] resident Table;


drop table Table;

output :

[stha142020]

Thanks! 

But I also have some cases where there isn't created a new row after cancellation:

Store number	Date	SlotStartTime	SlotEndTime	BookingFlag	CancelledFlag	Slot used
4	03.09.2020	12.00	13.30	1	1	0
4	03.09.2020	12.00	13.30	1	1	0
4	03.09.2020	12.00	13.30	1	1	0

And your solution tags these cases as 1 (used).