Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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());
Here you are... BookingsTest.qvw - Google Drive
There is no data in this file.
Hi Vishwarath,
Sorry, still trying to figure out a way to reduce the file size so i can send to you.
The reduced data function doesnt seem to do much for me
have you tried the solution which I have suggested.
Regards,
Yeah, sorry no results found using that one
https://drive.google.com/open?id=1k9i7Iy4cxBK9lQojqGRoJoKV7mUrHQdT
Here is a link to my file... i trimmed it down so that it only returned one Booking Reference.
You'll see on the script that each table has a [... Download Date], so ideally, i would save my excel document in my Qlikview folder, and then when the Dashboard builds, each [...Download Date] would only return dates contained within that excel file.
Does that make sense?
Appreciate the help
In your script
from Booking tab >
num([BD Download Date]) = num(Today()) and ([BD Booking Reference]) = 'A423512';
is this logic is working???
If yes then logic which i have suggested in my post must work. only think which you make sure is date field loading from excel is proper date if not then try using date#() to convert it into date format.
like num(date#(datefromExcel,'dateformat'))
Regards,
Hi Chris,
If none of the responses on this thread helped you in fixing issue in loading data from qvd, send me an email will catch up with you sometime to see what is happening if you are ok with it. You can get my email from my profile. Speak soon.
Thanks,
V.