Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
cmccafferty
New Contributor II

Return only specific dates from QVD?

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

1 Solution

Accepted Solutions
Highlighted

Re: Return only specific dates from QVD?

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,

Great dreamer's dreams never fulfilled, they are always transcended.

View solution in original post

27 Replies
Highlighted
vishsaggi
Esteemed Contributor III

Re: Return only specific dates from QVD?

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.

Highlighted
cmccafferty
New Contributor II

Re: Return only specific dates from QVD?

No luck

qvquery1.png

Highlighted
michaelm304
New Contributor

Re: Return only specific dates from QVD?

Amend;

ExcelDates:

LOAD ExcelfileColumnName As [BD Download Date]

From Excelfilename;

Highlighted
vishsaggi
Esteemed Contributor III

Re: Return only specific dates from QVD?

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.

Highlighted
cmccafferty
New Contributor II

Re: Return only specific dates from QVD?

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

qvquery2.png

Highlighted
vishsaggi
Esteemed Contributor III

Re: Return only specific dates from QVD?

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


Highlighted
cmccafferty
New Contributor II

Re: Return only specific dates from QVD?

Sorry, no luck with that either..

qvquery3.png

Highlighted
vishsaggi
Esteemed Contributor III

Re: Return only specific dates from QVD?

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

Highlighted
cmccafferty
New Contributor II

Re: Return only specific dates from QVD?