adding a column to script

[#1]

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.

[JonnyPoole]

Laurie can you copy/paste your entire script here?  Something is not adding up.

[#3]

  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

(ooxml

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

;

[JonnyPoole · Accepted solution]

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

[#5]

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

[JonnyPoole]

haha . no problem. glad it worked. it also helps to see a long list of what's been tried . good luck with QV !