12 Replies Latest reply: Mar 30, 2017 5:28 AM by chitral Chadda RSS

    Creating calendar

    chitral Chadda

      Hello All,

       

      I am trying to create a calendar , but it throws error basically it is not able to capture the date inside the variable vMinDate and vMaxDate. However if i do $(vMindate) and $(vmaxDate) it returns the date appropriately.

       

       

      Capture.PNG

      set vMinDate = date(min(date_va));

      set vMaxDate = date(max(date_va));

       

       

      test:

      LOAD * INLINE [

          id, date_va

          1, 02/11/2015

          2, 03/23/2016

          3, 11/20/2016

          4, 09/22/2017

          5, 05/22/2012

      ];

       

       

       

       

      tempcalendar:

      load

        $(vMinDate)  +IterNo()-1 as strtDateNum,

           $(vMinDate) +IterNo()-1 as startdate

           AutoGenerate 1

           while $(vMinDate)  + IterNo()-1 <= $(vMaxDate);

        • Re: Creating calendar
          Karthik Srinivasan

          Hi,

           

          It seems to be working as expected, could you elaborate on what it is that you are looking for?

            • Re: Creating calendar
              chitral Chadda

              Thanks karthik for prompt response.

               

              But it dosent work it throws error, basically i was looking for a short work around creating master calendar. So if i refer (IMG 1 below i wanted in vMinDate if i can get the values calculate from within the script/ field .

              my scipt

              set vMinDate = date(min(date_va));

              set vMaxDate = date(max(date_va));

               

              but when i reload it shows error  as shown in question

               

              IMG 1

              Capture.PNG

                • Re: Creating calendar
                  Anil Babu Samineni

                  May be date_va is the field is not working for you

                   

                  Can you check in Text box -- =$(vMinDate)

                  • Re: Creating calendar
                    Karthik Srinivasan

                    These are the issues am seeing:

                     

                    1. The 2 variables are referring to a field that is created after the variables are defined.

                    2. You cannot use date(min(date_va)) , this is how we'd use it in a chart, not in the script. In the script, you'd have to do a

                    temp_min_table:

                    load

                    min(date_va) as test_min_date,

                    max(date_va) as test_max_date

                    resident

                    test;

                    and then

                    set vMinDate = peek('test_min_date',-1,'temp_min_table');

                    set vMaxDate = peek('test_max_date',-1,'temp_min_table');

                     

                    You can then use the vMinDate and vMaxDate in your next reload.

                     

                    Hope this helps.

                      • Re: Creating calendar
                        chitral Chadda

                        yea i checked on this as an alternative i know this approach.

                        like we have to create a field and then load min max date in variable using peek function..

                        so i was being curious why can t we procees just by storing min/max in variable itself i was not confident with the reason as to why we cant do straight away..

                          • Re: Creating calendar
                            Karthik Srinivasan

                            It's just the way the Qlik syntax work. All aggregations in the script level happen via a load statement, in the chart level, they are similar to 'functions' that we can use.

                              • Re: Creating calendar
                                chitral Chadda

                                i could also have done this

                                Temp: 
                                Load 
                                min([Order Date]) as minDate
                                max([Order Date]) as maxDate 
                                FROM
                                [C:\Users\kuppili.anvesh\Qlikview Practice\Source\Superstore Subset.xlsx]
                                (
                                ooxml, embedded labels, table is Orders);


                                TempCalendar: 
                                LOAD 
                                minDate + Iterno()-1 As Num
                                Date(minDate + IterNo() - 1) as TempDate 
                                Resident Temp
                                 
                                While minDate + IterNo() -1 <= maxDate;  


                                would it make any difference in performance if I directly accesa fields minDate,maxDate above in TempCalendar

                              • Re: Creating calendar
                                Prashant Sangle

                                Hi,

                                 

                                try below

                                 

                                set vMinDate = '=date(min(date_va))';

                                set vMaxDate = '=date(max(date_va))';

                                 

                                Regards

                                  • Re: Creating calendar
                                    chitral Chadda

                                    set vMinDate = '=date(min(date_va))';

                                    set vMaxDate = '=date(max(date_va))';

                                     

                                    below error

                                    Capture.PNG

                                      • Re: Creating calendar
                                        Frank Hartmann

                                        Maybe like that:

                                         

                                         

                                        test:

                                            LOAD * INLINE [

                                                id, date_va

                                                1, 02/11/2015

                                                2, 03/23/2016

                                                3, 11/20/2016

                                                4, 09/22/2017

                                                5, 05/22/2012

                                            ];    

                                         

                                        Temp: 

                                        Load 

                                            min(date_va) as minDate, 

                                            max(date_va) as maxDate

                                        Resident test; 

                                         

                                        Let vMinDate = Num(Peek('minDate', 0, 'Temp')); 

                                        Let vMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

                                        DROP Table Temp; 

                                         

                                        tempcalendar:

                                        load

                                        $(vMinDate)  +IterNo()-1 as strtDateNum,

                                        $(vMinDate) +IterNo()-1 as startdate

                                        AutoGenerate 1

                                        while $(vMinDate)  + IterNo()-1 <= $(vMaxDate);

                                         

                                         

                                        hope this helps!