How to determine in an expression if a variable contains no value?

[splitcore]

Hi you Qliksperts ou there!

I want to determine in an expression if a variable contains NO value or string and perform an action based on that.

Currently I have a two multi boxes (Start Date & End Date)  which selects a certain year and writes the selection made into variables using on select trigger. I then use these variables which contain the years selected to populate a date range graph. This works fine.

My problem comes in when one or both multi boxes have no values selected.

I would like it to display only selected year of data if either m-box has a year selected, or data for all years when neither of the m-boxes have values selected.

When the multi boxes have no values selected obviously the variables have no value to link to either...

I have tried

If(isnull($(vStartDate)<>0)

If(isnull($(vStartDate)=0)

If(len('$(vStartDate)')=0)

The expressions work when there are values but even if I add an else like If(isnull($(vStartDate)=0, 'Has_value','Has_no_value' ) it fails. As if when the variable contains nothing NO action can be performed regardless of what is stated...

I only started using Qlikview about a two weeks ago so I'm still quite new at this. My logic just keeps telling me there has to be a way to perform an action if a variable is empty as empty itself is a state?

Kind Regards

[Gysbert_Wassenaar]

You could try If( Len(Trim( $(vStartDate) ))=0, 'No value', 'A value').

[splitcore]

Hi Gysbert

I tried your solution. It works with the Else if there is a value or ""Len(Trim( $(vStartDate) ))=4"" but if I put the expression into a list box to test, when the variable has no value ((When the multi box is blank)) the list box shows error:error in expression... So once again the expression fails when a value is completely absent.

I have even tried applying additional triggers, no luck. I'd need a trigger for "On Deselect" for it to work so I could give it some kind of value when it's deselected.

Have also tried additional variables that look at the primary variables vStartDate and vEndDate. I get the same issue where it works when the primary variables have values and fails when they don't.

And lastly I also tried expressions that compare to the value and check if it does NOT match "<>" instead of just matching "=".

So far my conclusion is that a variable with no value = A Black Hole for expressions... Perhaps it is a bug with Qlikview?

I'd think a variable should always have some kind of default value it returns to in these type of cases? In variable overview all it says in the value column for the variables is a single     -     ... And yes I have tried in the expressions to check for that symbol...ERROR

[Gysbert_Wassenaar]

Try this one then: If( Len(Trim( vStartDate ))=0, 'No value', 'A value')

[splitcore]

No luck

Without the $ sign before the variable it only displays 'A value' regardless of whether the variable contains a value or not.

[Peter_Cammaert · Accepted solution]

Try

If( Len(Trim( '$(vStartDate)' ))=0, 'No value', 'A value')

This will capture all cases of no value and vStartDate-does-not-exist (when you assign a NULL value to a variable). I guess the "error in expression" comes from the fact that when vStartDate doesn't exist, the $-sign expansion produces nothing. That makes the trim() call parameter-less and that's ... illegal.

Best,

Peter

[splitcore]

Hi Peter!

Good news is, Len(Trim( '$(vStartDate)' )) either returns 1 if the variable has no value or it returns 4 because of the year it looks at when a value is selected example 2004 = 4, "blank" = 1.

Bad news is I used this in my chart expression an encountered another wall... Perhaps I made an error in my expression that Qlikview misses, I will post my code just now. I use nested if statements to check for :

• If both m-boxes have a value selected,
• if neither m-boxes have a value selected and
• if either one or the other m-box has a value selected.

And if I test it with each If condition on it's own IT WORKS!! But when testing with the nested if it doesn't...

I should also mention that my YEAR field is simply a table of years, (NO MONTHS OR DATES) and HECTARE a table containing values associated with each specific year.

My expression as follows:

//Expression OK

If(Len(Trim( '$(vStartDate)' ))=4 and Len(Trim( '$(vEndDate)' ))=4,

SUM({<YEAR=>}If(YEAR>=$(vStartDate) AND YEAR<=$(vEndDate),HECTARE)),

If( Len(Trim( '$(vStartDate)' ))=1 and Len(Trim( '$(vEndDate)' ))=1,

SUM(HECTARE),

If(Len(Trim( '$(vStartDate)' ))=1 and Len(Trim( '$(vEndDate)' ))=4,

SUM({<YEAR=>}If(YEAR=$(vEndDate),HECTARE)),

If(Len(Trim( '$(vStartDate)' ))=4 and Len(Trim( '$(vEndDate)' ))=1,

SUM({<YEAR=>}If(YEAR=$(vStartDate),HECTARE))))))

I will mention again that these expressions work as intended when they are on their own so I don't know where I am going wrong... I even added them as separate expressions on the expressions tab, and AGAIN I have to disable the other three for any one to work... BUT THEY ALL WORK ...... Separately......

Edit: It seems only the If statement where both variables contain values work in the above expression along with the others. The other three still do not work. Shall I create a new question or leave as is? Because my original question was answered.

Kind Regards

[Peter_Cammaert]

Best practice says that you should indeed start a new discussion. OTOH this is just a continuation of the current thread that should (hopefully) lead to a working solution for you. And I may have a very straightforward solution, now that I think of it. So allow me for a little longer.

The idea is that you want to Sum field HECTARE for all rows that have a year between StartDate and EndDate? I don't quite get the meaning (or the content) of the two variables you're using, but why not simply use the active values in both m-boxes (selected or not!) as set limits? Like in (assuming that one m-box is called StartDate or better: StartYear, and the other EndYear😞

=SUM({<YEAR = {'>=$(=Min(StartYear))>=$(=Max(EndYear))'}>} HECTARE)

Of course, you'll get no results if StartYear ends up being larger than EndYear.

Best,

Peter

[splitcore]

Peter I tried your solution and could not get it to work with my data(For any of the conditions). If it did work, what would happen if I selected a value in StartYear m-box and not in EndYear? Wouldn't the range be from the StartYear selected to the end of the YEAR table instead of just the selected year?

I did find a workaround though... Not sure how resource friendly this will be if all my charts look like this in one qvd, is there a feature in Qlikview where a person could check which object or set of expressions gobble up the most resources? Anyhow here is what I did...

I split up the expressions in the expressions tab and gave each expression a conditional show.

Example in Conditional:

Trim( '$(vStartDate)' ))=4 and Len(Trim( '$(vEndDate)' ))=4

Example in Expression Definition:

SUM({<YEAR=>}If(YEAR>=$(vStartDate) AND YEAR<=$(vEndDate),HECTARE))

I still don't understand why I had to split them up for it to work... If they work like this they should in theory work in a nested if as well...

Regardless thank you for your assistance both you Peter and Gysbert! Although I feel like I now have more questions about how Qlikview looks at expressions than I did before I hope that gaining more experience will yield answers.

Kind Regards and Happy Holidays!