Convert P function to actions

[#1]

Hello,

I am new to QlikView. I have an existing Qlikview application that displays the summary figure in a textbox using following function

=Count({1 <Flag1 = {'1'}>
  - (1< [Unique_ID] = P({1 <Flag2 = {'1'}>})>)
  - (1< [Unique_ID] = P({1 <Flag3 = {'1'}>})>)
}
DISTINCT [Unique_ID])

Is there anyway that it can be converted into actions so that user can click on it to get function associated data?

Thanks in advance.

Henry

[danieloberbilli]

If you just would like to get a straight table with the remaining Unique_ID, I think the easiest way would just be a straight table with the Unique_ID as dimension and your textbox-expression as table expression:

Kind Regards

Daniel

[danieloberbilli]

If this is what you are looking for, you might set an action on the textbox so that the table is displayed when the user clicks on the textbox (see example attached)

[#4]

Thanks a lot Daniel for the help. The table provided is what we are working on. Is it possible to have the action(s) on the green textbox which doing the data filtering instead using a variable to open and close another table as in your example3? We have already had the fact tables as in example3. We just need to get those filtered data displayed on the table. In other words, can we have the action(s) that toggle the data on the fact data table according to the function provided?

[danieloberbilli]

Please see an example attached...check your results if this really works. I added following actions:

- clear all (as you use the '1' as SET IDENTIFIER to ignore all other selections

- Select field Flag1 =1

- Select field Flag2, 3 =0

!

[#6]

Hi Daniel, The answer is great. I am sorry that I have just realised that there is a mistake in my previous question. It is not a unique_id. Can you please add one more row to the fact table i.e. {4,1,0,1}? This will change calculation result to 1. However, when you click on it, it displays two records.

Furthermore, can we make it a "toggle" function instead of a "select in" function?

Thanks again.

[danieloberbilli]

Hi Henry,

In your calculation you use DISTINCT - which means, no matter how much IDs you have in the data - it will only count the IDs in the distinct way ...so I think, you wont get the 'true' number of cases that is shown in the fact table.

Maybe I get it wrong: but e.g. if you have 500 cases with the same ID - your calculation still shows =1 for this ID whereas the fact table still shows the 500 cases.

I am sorry I cannot give any advice regarding 'toggle selection' - I would need to have a much better understanding of your data model and what you would like to achieve with it.

Kind Regards

Daniel

[#8]

Hi Daniel,

Thanks for the reply. I think user is fine if there are more records on the fact table with the same ID. My question is that whether there is a way to get rid of the ID 4 as it has the entry of 1 with flag3. The total count appears to be correct as it only count the ID 5 and exclude the ID 4. In the "select in" or any other actions, is there anyway that we can exclude ID 4 so that it matches the total count record?

I think there is another way of walking around the issue. We can load another data table from a database view with dedicated new boolean flag. By choosing the boolean flag in the select in/toggle action, we can get the ID(s) that matches the data. 

Kind Regards,

Henry

[danieloberbilli · Accepted solution]

Hi Henry,

Please have a look at my new example attached. Probably not the most elegant way to script it - but maybe it helps to understand a possible approach that might work with your data model:

I create a new Flag called NotQualified to indicate which case should be ignored. Then I create the sum per UniqueID so that ID 4 is flagged as not qualified (although there would be one case with ID 4 which would qualify - but we want to ignore this one, right?) Then I simply use our Select-in-field-Action with NotQualified_sum = 0.

As I said - rather a quick&dirty script...but does it make sense?

d

[danieloberbilli]

btw...you can ignore the rename of Unique_ID to Unique_ID2 and just work all the time with Unique_ID