if condition in qlikview script where clause

[qliklearnervir]

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.

[sinanozdemir]

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:

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

Hope this helps.

[tamilarasu]

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)

[Anonymous]

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

[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

[Peter_Cammaert]

Are you sure?