identify one or more dates within one week

[heimo_ernst_wei]

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! 

[QFabian]

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;

[heimo_ernst_wei]

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. 

[PriyankaShivhare]

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;

[QFabian · Accepted solution]

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;

[heimo_ernst_wei]

Thank you this helped me to solve my problem.

[heimo_ernst_wei]

Thank you for your proposal, I found a solution.