topic Re: identify one or more dates within one week in QlikView

identify one or more dates within one week

heimo_ernst_wei — Fri, 15 Nov 2024 23:19:03 GMT

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!

Re: identify one or more dates within one week

QFabian — Thu, 14 Jan 2021 20:49:24 GMT

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;

Re: identify one or more dates within one week

heimo_ernst_wei — Mon, 18 Jan 2021 12:01:15 GMT

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.

Re: identify one or more dates within one week

PriyankaShivhare — Mon, 18 Jan 2021 12:51:11 GMT

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;

Re: identify one or more dates within one week

QFabian — Mon, 18 Jan 2021 12:56:30 GMT

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;

Re: identify one or more dates within one week

heimo_ernst_wei — Tue, 19 Jan 2021 15:58:47 GMT

Thank you this helped me to solve my problem.

Re: identify one or more dates within one week

heimo_ernst_wei — Tue, 19 Jan 2021 16:00:17 GMT

Thank you for your proposal, I found a solution.