topic Re: Return only specific dates from QVD? in QlikView

Return only specific dates from QVD?

cmccafferty — Thu, 02 Aug 2018 12:24:15 GMT

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

Re: Return only specific dates from QVD?

vishsaggi — Thu, 02 Aug 2018 12:41:49 GMT

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.

Re: Return only specific dates from QVD?

cmccafferty — Thu, 02 Aug 2018 14:46:44 GMT

No luck

Re: Return only specific dates from QVD?

Anonymous — Thu, 02 Aug 2018 14:53:50 GMT

Amend;

ExcelDates:

LOAD ExcelfileColumnName As [BD Download Date]

From Excelfilename;

Re: Return only specific dates from QVD?

vishsaggi — Thu, 02 Aug 2018 15:05:45 GMT

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.

Re: Return only specific dates from QVD?

cmccafferty — Thu, 02 Aug 2018 15:18:58 GMT

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

Re: Return only specific dates from QVD?

vishsaggi — Thu, 02 Aug 2018 15:25:03 GMT

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

Re: Return only specific dates from QVD?

cmccafferty — Thu, 02 Aug 2018 16:38:35 GMT

Sorry, no luck with that either..

Re: Return only specific dates from QVD?

vishsaggi — Thu, 02 Aug 2018 16:42:29 GMT

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

Re: Return only specific dates from QVD?

cmccafferty — Fri, 03 Aug 2018 08:20:38 GMT

Here you are..

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

Re: Return only specific dates from QVD?

cmccafferty — Fri, 03 Aug 2018 12:36:39 GMT

Anyone have any ideas?

Re: Return only specific dates from QVD?

Anonymous — Fri, 03 Aug 2018 12:41:35 GMT

It may need;

From Dateonly.xls

(biff, embedded labels);

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

Re: Return only specific dates from QVD?

vishsaggi — Fri, 03 Aug 2018 12:55:57 GMT

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

Re: Return only specific dates from QVD?

cmccafferty — Fri, 03 Aug 2018 15:06:54 GMT

Thanks for the suggestions guys, really appreciate it.

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

Re: Return only specific dates from QVD?

cmccafferty — Fri, 03 Aug 2018 15:19:48 GMT

Hi Vishwarath,

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

Would this be causing the issue?

Re: Return only specific dates from QVD?

vishsaggi — Fri, 03 Aug 2018 15:31:52 GMT

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

Re: Return only specific dates from QVD?

PrashantSangle — Fri, 03 Aug 2018 15:35:53 GMT

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,

Re: Return only specific dates from QVD?

cmccafferty — Mon, 06 Aug 2018 13:07:41 GMT

Hiya,

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

Re: Return only specific dates from QVD?

vishsaggi — Mon, 06 Aug 2018 14:29:07 GMT

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

Re: Return only specific dates from QVD?

vishsaggi — Mon, 06 Aug 2018 16:01:50 GMT

Can you also try like below once please?

LOAD .....

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

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