14 Replies Latest reply: Aug 18, 2014 5:07 PM by Jonathan Poole RSS

    adding a column to script

    Laurie Bayles

      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.

       

      qv image.bmp

        • Re: adding a column to script
          Tobias Klett

          Hi,

           

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

          Reload_Button.png

           

          Hope this helps

          Tobias

          • Re: adding a column to script
            gysbert wassenaar

            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
                Laurie Bayles

                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
                    Saradhi Balla

                    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

                      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 v

                    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
                        Laurie Bayles

                        short list.JPG.jpg

                        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
                            Jonathan Poole

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

                              • Re: adding a column to script
                                Laurie Bayles

                                  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


                                [G:\COMMON\Inventory Management\Inventory Analysis Team\Rental Report\Rental_Reservation_Report.xlsx]
                                (
                                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
                                ]

                                ;