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());

[cmccafferty]

Anyone have any ideas?

[Anonymous]

It may need;

From Dateonly.xls

(biff, embedded labels);

I know I've had some loads that are a little temperamental.

[vishsaggi]

ok so your date format is M/DD/YYYY so they are coming fine. Can you check just running your booking data.qvd and check what is the format for [BD Download Date] if it is the same format you do not need to convert to DD/MM/YYYY.

This should be very straight forward not sure why you getting this error. Probably try below script if not please load your qvd data into qlikview and share your qvw file i will have a look at it. You can scramble your data using below steps

https://community.qlik.com/docs/DOC-1290

Excel:

LOAD [BD Download Date] AS DownloadDate

FROM datesonly.xlsx;

Bookings:

LOAD *

where Exists(DownloadDate, [BD Download Date]);

LOAD

     [BD Booking Reference],

     [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]

Thanks for the suggestions guys, really appreciate it.

I've tried each of your suggestions but unfortunately no luck as yet.

[cmccafferty]

Hi Vishwarath,

I've just noticed that the BD Download Date is in the ##### format.

Would this be causing the issue?

[vishsaggi]

Yes. So do like below and let me know if that works.

LOAD Date#([BD Download Date], 'M/DD/YYYY') AS DownloadDate

FROM datesonly.xls

(biff, embedded labels, table is Sheet1$);

Bookings:

LOAD

     [BD Booking Reference],

     [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(DownloadDate, [BD Download Date]);

[PrashantSangle · Accepted solution]

See highlighted field.

ExcelDates:

LOAD num([BD Download Date]) as [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],num([BD Download Date]));

Regards,

[cmccafferty]

Hiya,

Okay that stopped the error, but Booking Data is returning 0

[vishsaggi]

Probably formatting issue. Would you be able to provide your sample data just by loading your bookings qvd into qlikview and share that file. You can mask the data in your qvw file using below instructions. This would be easy to work on my end and see what is happening.

Preparing examples for Upload - Reduction and Data Scrambling

[vishsaggi]

Can you also try like below once please?

LOAD .....

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

where Exists([BD Download Date], DownloadDate);