5 Replies Latest reply: Jan 21, 2016 3:51 AM by Peter Cammaert RSS

    if condition in qlikview script where clause

    vir vir

      Hi,

       

      In my script i want to declare and run the where clause according to some filter condition and criteria is:

       

       

      if weekday=Monday Then

       

      Load  ID,

               Name

      Where publish_start_date <= Today()-2

      and  publish_end_date <= Today();

       

      if other day than

       

      Load  ID,

               Name

      Where publish_start_date <= Today()

      and  publish_end_date <= Today();

       

       

      please suggest how can i create this statement or if possible than in one query can i create.

        • Re: if condition in qlikview script where clause
          Sinan Ozdemir

          Something like this should help you:

           

          Let vWeekday = WeekDay(Today());

           

          If '$(vWeekday)' = 'Mon' Then

               LOAD

                    ID,

                    Name

               Where publish_start_date <= Today()-2 and publish_end_date <= Today()

          Else

               LOAD

                    ID,

                    Name

               Where publish_start_date <= Today() and publish_end_date <= Today()

          Endif

           

          Here is a sample IF implementation:

          Capture.PNG

          In my example, I am only loading the data of Company A if the weekday is Wed.

           

          Hope this helps.

          • Re: if condition in qlikview script where clause
            Tamil Nagaraj

            Hi Vir,

             

            Something like below,

             

            If Weekday(Today())= 'Mon' Then 
            
            Load  ID,
                  Name
            Where publish_start_date <= Date(Today()-2 , 'DD/MM/YYYY') and  publish_end_date <= Today();
            
            Else
            
            Load  ID,
                  Name
            Where publish_start_date <= Today() and  publish_end_date <= Today();
            
            ENDIF
            

             

            Note: I assume your fields (publish_start_date, publish_end_date) date format as "DD/MM/YYYY". If not, you need format the Today() functions date format wherever it's used. ( Date(Today()), 'Your format)

            • Re: if condition in qlikview script where clause
              balraj ahlawat

              I think, its already been answered by Tamil Nagaraj     and  Sinan Ozdemir.

               

              Try their solution and close this thread, if you have got the answer

              • Re: if condition in qlikview script where clause
                Peter Cammaert

                You should weigh the advantage of writing shorter script code versus script code that performs better. AFAIK your original question was: can I embed this condition in the WHERE clause itself? The answer is  yes. But you may get worse performance than when leaving the condition out of the WHERE clause. For example, this works:

                 

                LOAD * INLINE [

                ID, publish_start_date, publish_end_date

                1, 1/1/2016, 10/1/2016

                2, 21/1/2016, 21/1/2016

                3, 31/1/2016, 15/2/2016

                ]

                WHERE publish_start_date <= (Today() + (WeekDay(Today()) = 0)*2)

                AND publish_end_date <= Today();

                 

                I don't know much about how QlikView optimizes LOAD statements. Worst case would be that it does not, and that the WHERE clause is evaluated for every single row in your source table. Since the calculation part never changes between rows, you would be losing precious time and energy in calculating the same result over and over.

                 

                In any case, a better solution would be to move the calculation out of the LOAD statement, like in:

                 

                LET vStartDateLimit = (Today() + (WeekDay(Today()) = 0)*2);

                 

                LOAD * INLINE [

                ID, publish_start_date, publish_end_date

                1, 1/1/2016, 10/1/2016

                2, 21/1/2016, 21/1/2016

                3, 31/1/2016, 15/2/2016

                ]

                WHERE publish_start_date <= $(vStartDateLimit)

                AND publish_end_date <= Today();

                 

                which calculates the Monday/OtherWeekDay limit only once. Note that we still use only one LOAD statement.

                 

                If you do not like this kind of script code compression, you can do as Balraj says and select on of the identical earlier responses. But you will have to type more text. And since your example may only reflect the issue and not necessarily the huge LOAD you are preparing, I thought it would be useful to mention this.

                 

                Best,

                 

                Peter