topic Re: Tag time slots used in App Development

Tag time slots used

stha142020 — Fri, 11 Sep 2020 08:06:20 GMT

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!

Re: Tag time slots used

Taoufiq_Zarra — Fri, 11 Sep 2020 09:02:03 GMT

@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 :

Re: Tag time slots used

stha142020 — Fri, 11 Sep 2020 12:53:25 GMT

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).