Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
stha142020
Contributor
Contributor

Tag time slots used

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 numberDateSlotStartTimeSlotEndTimeBookingFlagCancelledFlagSlot used
103.09.202012.0013.30110
103.09.202012.0013.30110
103.09.202012.0013.30000
203.09.202012.0013.30111
203.09.202012.0013.30101
303.09.202012.0013.30101

 

Any suggestions?

Thanks in advance!

2 Replies
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 :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
stha142020
Contributor
Contributor
Author

Thanks! 

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

Store numberDateSlotStartTimeSlotEndTimeBookingFlagCancelledFlagSlot used
403.09.202012.0013.30110
403.09.202012.0013.30110
403.09.202012.0013.30110

 

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