topic Re: adding a column to script in Water Cooler

adding a column to script

Mon, 18 Aug 2014 17:29:35 GMT

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.

Re: adding a column to script

tobias_klett — Mon, 18 Aug 2014 17:35:00 GMT

Hi,

QV loads the data into the applikation via a reload. To start this you need to hit the "Reload" Button.

Hope this helps

Tobias

Re: adding a column to script

Gysbert_Wassenaar — Mon, 18 Aug 2014 17:37:05 GMT

This may sound stupid, but did you reload the document after modifying the script? If so, does the Owner field show up in the table if you view it in the Table Viewer (File->Table Viewer...)?

Re: adding a column to script

Mon, 18 Aug 2014 17:40:05 GMT

No stupid questions when it comes to me . But I actually did hit the reload button. It does NOT show up in the Table Viewer. What does this mean? What should I do next?

Re: adding a column to script

Mon, 18 Aug 2014 17:40:57 GMT

I did hit the reload button but it didn't see to pull it in

Re: adding a column to script

Anonymous — Mon, 18 Aug 2014 17:50:36 GMT

i am assuming the script load completed successfully...it should have said something like xxx lines fetched. if there were an error, it would have said error loading data or something to that effect with an option to reload the old data. if you clicked yes, the old data set loads and you will not see the new field in the table viewer since the load failed.

fields are case-sensitive in qlikview, so i'm assuming the name of the column is spelled correctly. otherwise, load will fail.

Re: adding a column to script

Gysbert_Wassenaar — Mon, 18 Aug 2014 17:53:48 GMT

In that case check that you really added the new field to the script. After changing the script click the Save button to make sure the changes are applied. Reload the document and make sure no error message appears. Then check again if the new field is added.

Re: adding a column to script

muniyandi — Mon, 18 Aug 2014 17:57:02 GMT

Can you post your sample file. you are viewing in listbox ?. scroll the list box check once your new value are there or not.

Re: adding a column to script

Mon, 18 Aug 2014 18:02:19 GMT

I'm following you. Yes I hit the reload and everything was successful.

My field names look correct, do you see any problems?

I also confirmed I have saved my changes because the save icon is not highlighted, so I'm assuming everything is there.

Re: adding a column to script

Mon, 18 Aug 2014 18:13:05 GMT

I don't know how to export the list but here is a small screenshot. I just don't see it pulling in. How do I post the sample file from my listbox so you can help me?

Re: adding a column to script

JonnyPoole — Mon, 18 Aug 2014 19:56:07 GMT

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

Re: adding a column to script

Mon, 18 Aug 2014 20:04:53 GMT

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

ForcastSite

,
[1]

,
[2]

,
[3]

,
[4]

,
[5]

,
[6]

,
[7]

,
[8]

,
[9]

,
[10]

,
[11]

,
[12]

FROM

data\ForcastPerMonth.xlsx
(ooxml

, embedded

labels

, table

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

ForcastSite

,
Property

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

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

;

Re: adding a column to script

JonnyPoole — Mon, 18 Aug 2014 20:36:29 GMT

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

Re: adding a column to script

Mon, 18 Aug 2014 20:46:21 GMT

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

Re: adding a column to script

JonnyPoole — Mon, 18 Aug 2014 21:07:08 GMT

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