Combining Expressions in QV

[#1]

Good afternoon all,

I continue to struggle with combining expressions.

I have three expressions that I need to combine to get the correct list of items in my QV doc.

Here are the three. Each have been test and work individually but when combined I am getting unexpected results:

• =IF(([Has Active CDs-2700]='Y'),1,0)
• =IF(([Bank Status-2770]='Failed' OR [Bank Status-2770]='Merged') AND ([Merged Reports?-0450]='N'),1,0)
• =IF(([Bank Status-2770]='Failed' OR [Bank Status-2770]='Merged') AND ([Recip Balance-SLX]>=1000000),1,0)

It is important to note that the second and third expressions should add items to the results from the first. It should also be noted that each expression will include items that were not included in any of the previous expressions.

Each time I try to assemble these, I end up with fewer results than I had in the first expression which is not what I am looking for.

Therefore, I am doing something fundementally wrong in assembling these.

Can anyone offer any suggestions on how to assembel these to get what i am looking for?

As always, that for the help.

Steve

[Gysbert_Wassenaar]

Try this:

if( ([Has Active CDs-2700]='Y') OR

          (([Bank Status-2770]='Failed' OR [Bank Status-2770]='Merged') AND ([Merged Reports?-0450]='N')) OR

          (([Bank Status-2770]='Failed' OR [Bank Status-2770]='Merged') AND ([Recip Balance-SLX]>=1000000)),1,0)

Or this:

sum({<[Has Active CDs-2700]={'Y'}> +

        <[Bank Status-2770]={'Failed','Merged'},[Merged Reports?-0450]={'N'}> +

        <[Bank Status-2770]={'Failed','Merged'},[Recip Balance-SLX]={'>=1000000'}> 1)

[#3]

Thanks for the reply Gysbert.

These two efforts yeild similar results to what I have been getting myself.

The first one yeilds 107 records where the first expression itself yields over 1500 records. It is notable that with the first option I see only "merged" statuses and no Active or Failed statuses.

As for the second option, Again, I have had this problem all morning too, it yields no results at all. I can't even pick between "1" or "0" to test. Option two is simply non functional.

These are the two results I have been getting for the last two days. Either the results are limited to "Merged" (and more of the merged than should be there) or the code is just non functional.

Any other thoughts on this one?

Steve

Steve

[danielrozental]

Why would you get less results, doesn't really make sense to me. Extending on Gysbert ideas you could try something like this.

sum({<[Has Active CDs-2700]={'Y'}>} 1) +

sum({<[Bank Status-2770]={'Failed','Merged'},[Merged Reports?-0450]={'N'}>} 1) +

sum({<[Bank Status-2770]={'Failed','Merged'},[Recip Balance-SLX]={'>=1000000'}>} 1)

[#5]

I agree completely. Some of the results I get make no sense at all to me.

Let me explain further in case I have left something out or have made an error that needs to be fixed...

I created a table box to display all the desired results and then I created a multi-box where I have the available values of 1 and 0 to allow me to toggle and observe the results as a means of testing. I have also set up a handful of list boxes to aid in testing.

The script is being loaded into the multi-box as a custom field.

The code you supplied, as in some of my own cases, simply does not do anything. I cannot toggle in my multi box and select 1 or 0 and no results load. I just get the full unfiltered list.

Thanks everyone for your help so far though.

Steve

[danielrozental]

Why are you using a table box instead of a straight table?

There will be no way to select between 0 and 1 unless you add the conditions and create a field in the script, you could settle to add the dimension to a straight table and the expression to act as a filter.

[#7]

Hi Daniel,

Not to put too fine a a point on it, but this is the "new to Qlikview" forum and I am as new as it gets.

I made the choice I did because it seemed like a good idea at the time. I had tried creating a straight table and had the same problems there that I am having now.

I used the table box because it was easy to display the columns I wanted and to check them and to export the results as a spreadsheet (required for this project).

Since I am not using the expression in the table box, I am not sure how much that matters though. I am using the expression in a custom field in a multi box and the table box just displays the results.

I will create a straight table and try your suggestions and see if they work better there.

Thanks, Steve

[#8]

Good morning Daniel,

I created the straight table as you suggested and used several of the suggested formulas including yours.. I get exactly the same results as I do with the table box / multi-box.

Maybe what I am trying to do is just not possible, but I hope that it is.

I am open to suggestions.

Steve