Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Use field values to find associations, not filter

I'm looking for suggesions for how to use selected field values to find associated data, but not to restrict the data on those actual field values.  For instance, if I have a filter for order year, and i select 2015, i want to see data (in all reports in the doc) based on people who had orders in 2015, but not restrict all the data in the reports to 2015 orders.

I know I could accomplish this by creating a flag field via the script for each year in which the person has an order, but i was thinking there might be a more dynamic way, so that i don't have to adjust the script every year.  Maybe there's a really simple way that I'm just not seeing right now, so i'm asking the Community .

Appreciate any thoughts.  Thanks!

Tags (3)
1 Solution

Accepted Solutions
Not applicable

Re: Use field values to find associations, not filter

Hello, you may take a look at p() function:

=If(Count({<OrderYear=p(OrderYear)>} Order) > 0, <<Your expression here>>)

Actually this is: If(count of orders where order year = any possible order year taking into account selections, <<...>>)

This will work with selections, with no selections and all the ways around. But you have to update your expressions with set analysis p() function and IFs...

2 Replies
Not applicable

Re: Use field values to find associations, not filter

Hello, you may take a look at p() function:

=If(Count({<OrderYear=p(OrderYear)>} Order) > 0, <<Your expression here>>)

Actually this is: If(count of orders where order year = any possible order year taking into account selections, <<...>>)

This will work with selections, with no selections and all the ways around. But you have to update your expressions with set analysis p() function and IFs...

Not applicable

Re: Use field values to find associations, not filter

Hi,

You could also have an additional table (personyear).

  1. Orders:
  2. person & '|' & year As key
  3. person As p.person
  4. ....

  5. personyear:
  6. LOAD DISTINCT key,
  7. subfield(key,'|',1) As p.person,
  8. subfield(key,'|',2) As p.year //use this in list box
  9. RESIDENT Orders;
  10. Drop field key;


Ooh.. too tired. Did I get it even close to right?