Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am very new to Qlikview so I hope I understand the answers. I have added an additional column to my excel that I pull in as my data source. The new column is called Owner, this shows me which sales manager owns the account. I added to the script (i think) but it does not appear and when I go to sheet properties and try to add a new field.
Laurie can you copy/paste your entire script here? Something is not adding up.
This is driving me crazy, I can't say thank you enough for trying to help me.
SET
ThousandSep
=',';
SET
DecimalSep
='.';
SET
MoneyThousandSep
=',';
SET
MoneyDecimalSep
='.';
SET
MoneyFormat
='$#,##0.00;($#,##0.00)';
SET
TimeFormat
='h:mm:ss TT';
SET
DateFormat
='MM/DD/YYYY';
SET
TimestampFormat
='MM/DD/YYYY h:mm:ss[.fff] TT';
SET
MonthNames
='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET
DayNames
='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET
HidePrefix
= '_';
Let
vReportType
='';
Site:
LOAD
* INLINE
[
Site,Property
1, 1-Orlando, FL
2, 2-Lake Geneva, WI
3, 3-Brownsville, VT
4, 4-Panama City, FL
6, 6-Myrtle Beach, SC
7,7-Gatlinburg, TN
8,8-Las Vegas, NV
9,9-Marco Island, FL
10,10-Galveston, TX
11,11-Panama City Beach, FL
12,12-Williamsburg, VA
13,13-Cape Canaveral, FL
]
;
SitesForcastPerMonth_t:
Crosstable
(ForcatMonth
,ForcatRevenue
)
LOAD
site
as
ForcastSite
,
[1]
,
[2]
,
[3]
,
[4]
,
[5]
,
[6]
,
[7]
,
[8]
,
[9]
,
[10]
,
[11]
,
[12]
FROM
data\ForcastPerMonth.xlsx
(ooxml
, embedded
labels
, table
is
Sheet1);
SitesForcastPerMonth:
LOAD
*,
'2014' as
ForcastYear
,
Num
(if
(len
(ForcatMonth
)=1,'0'&ForcatMonth
,''&ForcatMonth
),'00') as
ForcatMonthNum
,
MOnthname
(date
(Num
(if
(len
(ForcatMonth
)=1,'0'&ForcatMonth
,''&ForcatMonth
),'00')&'/01/2014','MM/DD/YYYY')) as
ForcastMonthName
,
ForcastSite
&'-'&MOnthname
(date
(Num
(if
(len
(ForcatMonth
)=1,'0'&ForcatMonth
,''&ForcatMonth
),'00')&'/01/2014','MM/DD/YYYY')) as
Index_Site_ForcastMonthName
Resident
SitesForcastPerMonth_t;
left
join
LOAD
Site
as
ForcastSite
,
Property
as
ForcastProperty
Resident
Site;
drop
table
SitesForcastPerMonth_t;
holidayInnClubData:
LOAD
Site
,
[Arrival Year]
,
[Arrival Month]
,
[Day of Month Arrival]
,
[Arrival Week]
,
[Reservation ID]
,
[Reservation Number]
,
[Booking Date]
,
[Scheduled Arrival Date]
,
Nights
,
Revenue
,
[Room Type]
,
[IHG Rate Source]
,
SubType
,
Source
,
Source2
,
Status
,
[Company Name]
,
[Iata Number]
,
[Booking Week]
,
[First Name]
,
[Last Name]
,
Owner
FROM
(
, embedded
labels
, table
is
[Report 1]);
//lauire please change path above to real report path the way we saw
// remember to remove the relative path checkbox before selecting the Table files... button
// remember to delete the extra line you add to take the real path
store
holidayInnClubData into
data\holidayInnClubData.qvd;
drop
table
holidayInnClubData;
MaxBookDateThisY_M:
MAPPING
LOAD
1 as
index
,
Max
(date
([Booking Date]
,'MM/DD/YYYY')) as
Max_BookingDate
FROM
data\holidayInnClubData.qvd
(qvd
);
let
vLastYMaxDateYTD
=date
(ApplyMap
('MaxBookDateThisY_M',1)-365,'MM/DD/YYYY');
let
vLastYMaxDateLast7
=date
(ApplyMap
('MaxBookDateThisY_M',1)-372,'MM/DD/YYYY');
let
vThisYMaxDateYTD
=date
(ApplyMap
('MaxBookDateThisY_M',1),'MM/DD/YYYY');
let
vThisYMaxDateLast7
=date
(ApplyMap
('MaxBookDateThisY_M',1)-7,'MM/DD/YYYY');
holidayInnClubData:
LOAD
Site
,
[Arrival Year]
,
if
((ceil
(month
(date
([Scheduled Arrival Date]
,'MM/DD/YYYY')))/3)>0,'Q' & ceil
(month
(date
([Scheduled Arrival Date]
,'MM/DD/YYYY'))/3)) as
[Arrival Quarter]
,
// Num([Arrival Month],'00')
Month
(date
([Scheduled Arrival Date]
,'MM/DD/YYYY')) as
[Arrival Month]
,
// Month(date([Arrival Month],'MM/DD/YYYY')) as [Arrival MonthN],
MonthName
(date
([Scheduled Arrival Date]
,'MM/DD/YYYY')) as
[Arrival MonthName]
,
[Day of Month Arrival]
,
Num
([Arrival Week]
,'00') as
[Arrival Week]
,
date
([Scheduled Arrival Date]
,'MM/DD/YYYY') as
[Arrival Date]
,
num
(date
([Scheduled Arrival Date]
,'MM/DD/YYYY')-date
([Booking Date]
,'MM/DD/YYYY')) as
[bw]
,
[Reservation ID]
,
[Reservation Number]
,
date
([Booking Date]
,'MM/DD/YYYY') as
[Booking Date]
,
day
(date
([Booking Date]
,'MM/DD/YYYY')) as
[Booking Day]
,
if
(date
([Booking Date]
,'MM/DD/YYYY')>=YearStart
(date
('$(vLastYMaxDateYTD)','MM/DD/YYYY')) and
date
([Booking Date]
,'MM/DD/YYYY')<=date
('$(vLastYMaxDateYTD)','MM/DD/YYYY'),'MTD',
if
(date
([Booking Date]
,'MM/DD/YYYY')>=YearStart
(date
('$(vThisYMaxDateYTD)','MM/DD/YYYY')),'MTD','0')) as
_BookDateMTDReportType
,
if
(date
([Booking Date]
,'MM/DD/YYYY')>=date
('$(vLastYMaxDateLast7)','MM/DD/YYYY') and
date
([Booking Date]
,'MM/DD/YYYY')<=date
('$(vLastYMaxDateYTD)','MM/DD/YYYY'),'Last7',
if
(date
([Booking Date]
,'MM/DD/YYYY')>=date
('$(vThisYMaxDateLast7)','MM/DD/YYYY'),'Last7','0')) as
_BookDateLast7ReportType
,
Year
(date
([Booking Date]
,'MM/DD/YYYY')) as
[Booking Year]
,
Month
(date
([Booking Date]
,'MM/DD/YYYY')) as
[Booking Month]
,
MonthName
(date
([Booking Date]
,'MM/DD/YYYY')) as
[Booking MonthName]
,
Site
&'-'&MonthName
(date
([Scheduled Arrival Date]
,'MM/DD/YYYY')) as
Index_Site_ForcastMonthName
,
if
((ceil
(month
(date
([Booking Date]
,'MM/DD/YYYY')))/3)>0,'Q' & ceil
(month
(date
([Booking Date]
,'MM/DD/YYYY'))/3)) as
[Booking Quarter]
,
week
(date
([Booking Date]
,'MM/DD/YYYY'))as
[Booking Week]
,
Nights
,
Revenue
,
[Room Type]
,
[IHG Rate Source]
,
if
(SubType
='IHG MACRO' or
SubType
='IHG CRO','IHG CALL',SubType
) as
SubType
,
Source
,
Source2
,
Status
,
[Company Name]
,
if
(index
(Upper
([Company Name]
),'google')>0,'google',if
(index
(Upper
([Company Name]
),'tourico')>0,'Tourico',if
(index
(Upper
([Company Name]
),'America Express')>0,'America Express',[Company Name]
))) as
Company
,
[Iata Number]
,
[First Name]
,
[Last Name]
FROM
data\holidayInnClubData.qvd
(qvd
);
SeasonalityViewType:
LOAD
* INLINE
[
_ViewTypeExp
Reservations#
Revenue
Room Nights
]
;
Owner is being loaded into a table called holidayInnClubData and the table is stored as a QVD and then the whole table is dropped here:
drop table holidayInnClubData;
Then lower down you are reloading the fields from that same QVD (but not owner) .
What to do ?
Near the bottom of the script you will see these lines:
....
[First Name],
[Last Name]
FROM data\holidayInnClubData.qvd (qvd);
change to:
....
[First Name],
[Last Name],
Owner
FROM data\holidayInnClubData.qvd (qvd);
and try to reload
Jonathan, I can't say thank you enough!!!!!!!!! I have worked on this all day and didn't expect to be able to fix it. YOU DID it!!!!! Thank you soooo much. IT WORKED
haha . no problem. glad it worked. it also helps to see a long list of what's been tried . good luck with QV !