topic Re: Issue with an if in QlikView

Issue with an if

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have some pretty big nested Ifs' with which I assign the amount of working days to each month that's something like this:

if(assign_date_month = 'Jan', 16

if(assign_date_month = 'Feb',20

if(assign_date_month = 'Mar',23

if(assign_date_month = 'Apr',18

if(assign_date_month = 'May',22

if(assign_date_month = 'June',22

if(assign_date_month = 'July',21

if(assign_date_month = 'Aug',22

if(assign_date_month = 'Sept' and assign_date <= '11-09-17',6

if(assign_date_month = 'Sept' and assign_date >= '11-09-17',14

if(assign_date_month = 'Oct', 22

if(assign_date_month = 'Nov', 21

if(assign_date_month = 'Dec', 18

,if(assign_date_month = 'Jan' and Year(assign_date) = 2018,5)

)))))))))))))

The exception on September is due to the fact that September is split between two work cycles(Cycle 2 is until 11-09-17 and Cycle 3 is after 11-09-17), that I can manipulate via multibox. My problem is, apparently it doesn't work, because regardless if I select Cycle 2 or Cycle 3, the September value for working days is considered null.
TL;DR: I want my value for working days in September to change depending on what cycle I select(Cycle 2 and 3 in this case). Cycle 2 is until the date of 11-09-2017 and Cycle 3 is after the date of 11-09-2017. How can I do that, preferably in the nested Ifs?

Re: Issue with an if

shiveshsingh — Mon, 29 Jan 2018 09:45:30 GMT

Can you share some sample data?

What's the format for assign_date? Try using Date function once

Re: Issue with an if

Peter_Cammaert — Mon, 29 Jan 2018 09:45:59 GMT

If assign_date = 11-09-17, you try to stuff two distinct values (6 and 14) in whatever field or table cell you are applying this expression to. That won't work. I think (according to your explanation) that the second if condition should omit the equal sign.

Re: Issue with an if

Anonymous — Mon, 29 Jan 2018 09:47:32 GMT

The format for assign_date is 'DD-MM-YY'

Re: Issue with an if

Peter_Cammaert — Mon, 29 Jan 2018 09:48:05 GMT

Also make sure that your default date format accepts 11-09-17 as a date string. Check global variable DateFormat for the correct format string.

Re: Issue with an if

Anonymous — Mon, 29 Jan 2018 09:50:23 GMT

So something like:

if(assign_date_month = 'Sept' and assign_date <= '11-09-17',6

if(assign_date_month = 'Sept' and assign_date > '11-09-17',14)
?

Re: Issue with an if

Anonymous — Mon, 29 Jan 2018 09:51:45 GMT

Also the Date Format is set correctly to DD-MM-YY

Re: Issue with an if

Peter_Cammaert — Mon, 29 Jan 2018 10:52:01 GMT

Now it depends on context (for which we don't get any details). If you now select september, then how many different dates will go into your expression? This expression will work just fine for september if you use it in a straight table in which assign_date is a dimension.

Re: Issue with an if

Anonymous — Mon, 29 Jan 2018 11:18:15 GMT

I am using it in a pivot table, with assign_date_month and area as dimensions, and the nested ifs are used in an expression to assign the working days for every month.

Re: Issue with an if

Anonymous — Mon, 29 Jan 2018 11:31:53 GMT

Managed to find a workaround by replacing the date comparisons with WildMatch(Concat(....