How do you filter a calculated field?

[#1]

Hi,  I'm new to QlikView...

My question is, how do you filter a calcuated field?  I have the following scenio:

When posting payments, the "Payment Facility" should match the "Claim Facilty".

The following straight table was created:

Claim Facility  |  Payment Facility

Facility A  |  Facility A

Facility A  |  Facility A

Facility A  |  Facility B

Facility B  |  Facility B

I added a calculated field to show when the Claim Facility did not equal the Payment Facility:

Claim Facility  |  Payment Facility  |  FacilityMatch?

Facility A  |  Facility A  |  Yes

Facility A  |  Facility A  |  Yes

Facility A  |  Facility B  |  No

Facility B  |  Facility B  |  Yes

So what I need QlikView to do, is to allow the user to have the option to filter (listbox):

FacilityMatch

Yes

No

And... allow the user to view either all the Yes rows:

Facility A  |  Facility A  |  Yes

Facility A  |  Facility A  |  Yes

Facility B  |  Facility B  |  Yes

Or... all the No rows:

Facility A  |  Facility B  |  No

Note:  This is a simple task in Excel.  As soon as I export the list to excel and turn on filters... there it is... Yes/No filter options.  So how does this same functionality work in QlikView?

Any assistance on this would be greatly appreciated.

[Gysbert_Wassenaar]

The easiest way is to calculate the field in the script. Something like:

T1:

load *, if([Claim Facility]=[Payment Facility],'Yes','No') as FacilityMatch

Inline [

Row, Claim Facility  ,  Payment Facility

1,Facility A  ,  Facility A

2,Facility A  ,  Facility A

3,Facility A  ,  Facility B

4,Facility B  ,  Facility B];

Because FacilityMatch is a field you can select a value in the field.

If you want to do this in a chart you can use if([Claim Facility]=[Payment Facility],'Yes') to show only the matching records or if([Claim Facility]<>[Payment Facility],'No') to show the non-matching records. Clicking on a value in an expression won't select the value of the expression, but the values of the dimension values in the same row.

See attached example.

[#3]

Thank you for the fast reply.

My only question is, I don’t understand how the row:  1 , 2, 3, and 4 would apply in my situation.

We currently have ~30 facilities, the combinations are endless (and the table source data can be updated/changed at any time).

My question is:

Does Claim Facility = Payment Facility in each row listed?  Then allow the user to view:

·        All rows

·        Only the ones that matched (Yes)

·        Only the ones that didn’t match (No)

Can I still use the below recommendation… but not have to key in all of the possible combinations?

[#4]

Your attached QV example worked perfectly.  I was able to add a list box and choose No’s only.

So how can your Load Script be changed to work for me?

T1:

load *, if([Claim Facility]=[Payment Facility],'Yes','No') as FacilityMatch …?

You added row data, but mine creates rows from loaded table fields:

Loaded field name:  ClaimFacilityName;  Column renamed in QV:  Claim Facility

Loaded field name:  FacilityName;  Column renamed in QV:  Payment Facility

I have tried too many combinations of your script… and I still can’t get it to work in my script.  It loads 0 lines or errors on the reload.

[#5]

Hi ,

      Please correct me .

    I am,providing the solution as per my understanding .

You can have expression in  the ListBox as

=if([ClaimFacility] = [PaymentFacility], 'Yes', 'No')

So when you select 'Yes' in ListBox ,  the Matched Rows get selected .

and when you select 'No' in ListBox  , the Non Matched Rows get selected .

Please let me know if there is anything.

[#6]

I tried an expression list box… it seemed like it was going to work, then when I choose “No”, my Yes’s and No’s were still there and the current selections box displayed:

Claim Facility: All

Payment Facility: 19 of 44

I would like it to display:

FacilityMatch: No

And just show me the “No” rows.

I have also tried the load inline statement recommended above… and I don’t know how to apply it to my QV. ie… The row data included in the example script, doesn’t apply to my QV. The rows were keyed in. My rows comes from fields from loaded tables. I don’t know how to take the Row lines and change the script to meet my needs. The attached QV example is working just like how I would like my QV to work. How to I apply the same script to loaded tables?

Any further assistance would be GREATLY appreciated.

[#7]

Hi ,

As you said After selecting the 'Yes' in Listbox still it is sowing all values even though you have used the Expression =if([ClaimFacility] = [PaymentFacility], 'Yes', 'No') in the List Box.

Could you please provide the Screen Shot .

So that i can be helpful to resolve your issue .

Regards

[er_mohit]

Hii

see the attached file

[#9]

Hi

I also came across this problem, I did two calculate field in straight table and the drill-down button

didn't work well

a would like to know if someone have any idea

BR