Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
qliklearnervir
New Contributor III

if condition in qlikview script where clause

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.

5 Replies
sinanozdemir
Valued Contributor III

Re: if condition in qlikview script where clause

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

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)

balrajahlawat
Esteemed Contributor

Re: if condition in qlikview script where clause

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

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

Re: if condition in qlikview script where clause

Are you sure?

Community Browser