Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table representing a booking journal, bookings are sometimes made on one day per week only (not always the same weekday), sometimes on two days within one week. There are some thousand bookings per batch run (day).
I need to extract the bookings of only one day per week.
Looking for a solution to extract/identify only one date per week even if two have been stored.
Thank you in advance for any useful proposal!
Hi @heimo_ernst_wei , in that case, you can do something like this :
BookingJournal:
Load
BookingID & ' | ' & week(DateOfBooking) as Id,
DateofBooking,
MoreFields
From yoursource;
MinDatePerWeek:
Load
Id,
min(DateOfBooking) as MinDateofWeek
Resident BookingJournal
group by Id;
Hi @heimo_ernst_wei , please check if something like this works for you.
Its just creates the field NumberOfDates, and its going to tell you how many dates are in every Booking :
BookingJournal:
Load
BookingID & ' | ' & week(DateOfBooking) as Id,
DateofBooking,
MoreFields
From yoursource;
NumberOfDates:
Load
Id,
count(DateOfBooking) as NumberOfDates
Resident BookingJournal
group by Id;
Thank you for your proposal, however I think I did not explain my problem well enough.
I have bookings from one or two different dates in a week and want use only one date per week for aggregation.
If i understand it right
this mght work for you:
Map_BookingIdCount:
Mapping Load Week
count(Distinct BookingID) as BookingCount
Source Booking Table
group by week;
Booking:
Load *,
if(BookingCount=1,1,0) _OneBookingperWeekFlag;
Load Week
applymap('Map_BookingIdCount',Week,null()) as BookingCount,
more Fields
Source Booking Table;
Hi @heimo_ernst_wei , in that case, you can do something like this :
BookingJournal:
Load
BookingID & ' | ' & week(DateOfBooking) as Id,
DateofBooking,
MoreFields
From yoursource;
MinDatePerWeek:
Load
Id,
min(DateOfBooking) as MinDateofWeek
Resident BookingJournal
group by Id;
Thank you this helped me to solve my problem.
Thank you for your proposal, I found a solution.