Return only specific dates from QVD?

[cmccafferty]

Hi,

Can someone look at my script below and advise me on how to significantly reduce the file size by only bringing home a selection of [BD Download Date]s.

My normal script brings back every [BD Download] date, but instead i want to just bring back a list of about 30 or so dates (FYI, each BD Download date contains all bookings).

At the bottom of my script I have highlighted in red the bit i have added on that will bring back just yesterday's date... but is it possible to bring back a range of dates, say that I would have in a small *.xls file or *.txt file?

Any help would be greatly appreciated.

Bookings:

LOAD

     [BD Booking Reference],

     date([BD Download Date], 'DD/MM/YYYY') as [BD Download Date],

     [BD Tour ID] & '|' & date([BD Download Date], 'DD/MM/YYYY') as UniqueTourID,

     [BD Booking Reference] & '|' & date([BD Download Date], 'DD/MM/YYYY') as UniqueBookingID,

     [BD Tour ID],

     [BD Unique TourDate ID],

     [BD Unique BookingRefDate ID],

     if([BD ABTA]= '', [BD Channel], 'Agent') as [Channel],

     if([BD Cancellation Type] ='Option Cancelled' and [BD Booking Status] = 'Cancelled', 'Cancelled Option',

         if([BD Cancellation Type] <> 'Option Cancelled' and [BD Booking Status] = 'Cancelled', 'Cancelled Client/Company',

         [BD Booking Status])) as [Booking Status],

     [BD Cancellation Type],

     [BD Pax],

     if([BD Booking Status] = 'Confirmed', [BD Pax], 0) as [Confirmed Pax],

     date([BD Optioned Date], 'DD/MM/YYYY') as [BD Optioned Date],

     date([BD Confirmed Date], 'DD/MM/YYYY') as [BD Confirmed Date],

     date([BD Cancellation Date], 'DD/MM/YYYY') as [BD Cancellation Date],

     [BD OptionBy],

     [BD Lead Name],

     [BD ABTA],

     [BD Business Name],

     [BD Agency Commission Group],

     [BD Agency Billing Group],

     [BD Insured Pax],

     [BD Total Revenue],

     [BD Total Insurance Revenue],

     [BD Credit Card Revenue],

     [BD Cancellation Revenue],

     [BD Comission],

     [BD VAT Commission],

     [BD VAT Rate],

     [BD Money Received],

     [BD Deposit Expected],

     date([BD Balance Due Date], 'DD/MM/YYYY') as [BD Balance Due Date],

     [BD Balance],

     [BD Total Costs],

     [BD Excursions Revenue],

     ([BD Total Revenue] - [BD Excursions Revenue] - [BD Total Insurance Revenue]) as [Overall Revenue],

     [BD BasicSellingPrice],

     [BD PricingType],

     [BD FILOType],

     date([BD ReturnDate], 'DD/MM/YYYY') as [BD ReturnDate]

FROM [..\Data\BookingData.qvd] (qvd)

where num([BD Download Date]) = num(Today());

[vishsaggi]

May be you can try like:

ExcelDates:

LOAD [BD Download Date]

From Excelfilename;

Bookings:

LOAD

     [BD Booking Reference],

     date([BD Download Date], 'DD/MM/YYYY') as [BD Download Date],

     [BD Tour ID] & '|' & date([BD Download Date], 'DD/MM/YYYY') as UniqueTourID,

     [BD Booking Reference] & '|' & date([BD Download Date], 'DD/MM/YYYY') as UniqueBookingID,

     [BD Tour ID],

     [BD Unique TourDate ID],

     [BD Unique BookingRefDate ID],

     if([BD ABTA]= '', [BD Channel], 'Agent') as [Channel],

     if([BD Cancellation Type] ='Option Cancelled' and [BD Booking Status] = 'Cancelled', 'Cancelled Option',

         if([BD Cancellation Type] <> 'Option Cancelled' and [BD Booking Status] = 'Cancelled', 'Cancelled Client/Company',

         [BD Booking Status])) as [Booking Status],

     [BD Cancellation Type],

     [BD Pax],

     if([BD Booking Status] = 'Confirmed', [BD Pax], 0) as [Confirmed Pax],

     date([BD Optioned Date], 'DD/MM/YYYY') as [BD Optioned Date],

     date([BD Confirmed Date], 'DD/MM/YYYY') as [BD Confirmed Date],

     date([BD Cancellation Date], 'DD/MM/YYYY') as [BD Cancellation Date],

     [BD OptionBy],

     [BD Lead Name],

     [BD ABTA],

     [BD Business Name],

     [BD Agency Commission Group],

     [BD Agency Billing Group],

     [BD Insured Pax],

     [BD Total Revenue],

     [BD Total Insurance Revenue],

     [BD Credit Card Revenue],

     [BD Cancellation Revenue],

     [BD Comission],

     [BD VAT Commission],

     [BD VAT Rate],

     [BD Money Received],

     [BD Deposit Expected],

     date([BD Balance Due Date], 'DD/MM/YYYY') as [BD Balance Due Date],

     [BD Balance],

     [BD Total Costs],

     [BD Excursions Revenue],

     ([BD Total Revenue] - [BD Excursions Revenue] - [BD Total Insurance Revenue]) as [Overall Revenue],

     [BD BasicSellingPrice],

     [BD PricingType],

     [BD FILOType],

     date([BD ReturnDate], 'DD/MM/YYYY') as [BD ReturnDate]

FROM [..\Data\BookingData.qvd] (qvd)

where Exists([BD Download Date]) ;

Make sure you have the same date format as your excel and Bookings table.

[cmccafferty]

No luck

[Anonymous]

Amend;

ExcelDates:

LOAD ExcelfileColumnName As [BD Download Date]

From Excelfilename;

[vishsaggi]

Does your excel sheet has the field name [BD Download Date] if can you tell me the field where the dates are coming? may be rename it like

LOAD Datefieldfromexcel AS [BD Download Date]

FROM excelfilename;

Hope you get it.

[cmccafferty]

this is what my excel file looks like and the file is called "datesonly.xls"

[vishsaggi]

Try this?

LOAD

Date(date#([BD Download Date], 'DD/MM/YYYY'),'DD/MM/YYYY') AS [BD Download Date]

FROM Datesonly.xls;

Bookings:

LOAD *

where Exists([BD Download Date]);

LOAD

     [BD Booking Reference],

     date([BD Download Date], 'DD/MM/YYYY') as [BD Download Date],

     [BD Tour ID] & '|' & date([BD Download Date], 'DD/MM/YYYY') as UniqueTourID,

     [BD Booking Reference] & '|' & date([BD Download Date], 'DD/MM/YYYY') as UniqueBookingID,

     [BD Tour ID],

     [BD Unique TourDate ID],

     [BD Unique BookingRefDate ID],

     if([BD ABTA]= '', [BD Channel], 'Agent') as [Channel],

     if([BD Cancellation Type] ='Option Cancelled' and [BD Booking Status] = 'Cancelled', 'Cancelled Option',

         if([BD Cancellation Type] <> 'Option Cancelled' and [BD Booking Status] = 'Cancelled', 'Cancelled Client/Company',

         [BD Booking Status])) as [Booking Status],

     [BD Cancellation Type],

     [BD Pax],

     if([BD Booking Status] = 'Confirmed', [BD Pax], 0) as [Confirmed Pax],

     date([BD Optioned Date], 'DD/MM/YYYY') as [BD Optioned Date],

     date([BD Confirmed Date], 'DD/MM/YYYY') as [BD Confirmed Date],

     date([BD Cancellation Date], 'DD/MM/YYYY') as [BD Cancellation Date],

     [BD OptionBy],

     [BD Lead Name],

     [BD ABTA],

     [BD Business Name],

     [BD Agency Commission Group],

     [BD Agency Billing Group],

     [BD Insured Pax],

     [BD Total Revenue],

     [BD Total Insurance Revenue],

     [BD Credit Card Revenue],

     [BD Cancellation Revenue],

     [BD Comission],

     [BD VAT Commission],

     [BD VAT Rate],

     [BD Money Received],

     [BD Deposit Expected],

     date([BD Balance Due Date], 'DD/MM/YYYY') as [BD Balance Due Date],

     [BD Balance],

     [BD Total Costs],

     [BD Excursions Revenue],

     ([BD Total Revenue] - [BD Excursions Revenue] - [BD Total Insurance Revenue]) as [Overall Revenue],

     [BD BasicSellingPrice],

     [BD PricingType],

     [BD FILOType],

     date([BD ReturnDate], 'DD/MM/YYYY') as [BD ReturnDate]

FROM [..\Data\BookingData.qvd] (qvd);

[cmccafferty]

Sorry, no luck with that either..

[vishsaggi]

It says field not found from your excel. Are there any spaces or anything. Can you share this excel file please?

[cmccafferty]

Here you are..

https://drive.google.com/file/d/1aBbyoVUrRLWFHdsqCqHKrAe_Za0Q1ACm/view?usp=sharing