topic Re: if condition in qlikview script where clause in QlikView

if condition in qlikview script where clause

qliklearnervir — Thu, 21 Jan 2016 03:00:08 GMT

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

sinanozdemir — Thu, 21 Jan 2016 03:28:11 GMT

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.

Re: if condition in qlikview script where clause

tamilarasu — Thu, 21 Jan 2016 06:32:11 GMT

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

Anonymous — Thu, 21 Jan 2016 06:45:46 GMT

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 — Thu, 21 Jan 2016 08:46:35 GMT

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

Peter_Cammaert — Thu, 21 Jan 2016 08:51:18 GMT

Are you sure?