topic Re: Create Listbox with values debending on AND selection in another Listbox in QlikView

Create Listbox with values debending on AND selection in another Listbox

daniel_werndl — Fri, 16 Jan 2015 09:19:12 GMT

Hallo, I'm fairly new to QlikView,

Im trying to list Customers that made a purchase in the year 2013 and 2014. the Years are selected with an AND-Listbox.

The year- Listbox ist straight forward. The field 'Name' is unique and Joins with a Orders Table.(irrelevant for now)

andYear:

LOAD DISTINCT

Name

resident Rechnung;

JOIN (andYear) LOAD Year as customerYear Resident MasterCalendar;

For the Customer Listbox I was thinking of something like 'select Customer where there is a purchase in the selected years'

I've tried: =($($<Year=>) Name)

Im guessing I'm on the completely wrong track.

Could you please help me out? Seems to be a simple task.

Best Regards

Daniel

Re: Create Listbox with values debending on AND selection in another Listbox

Fri, 16 Jan 2015 09:44:23 GMT

Hi Daniel,

It seems a bit confusing that what is your actual requirement.

Kindly u describe it simply.

Regards

Re: Create Listbox with values debending on AND selection in another Listbox

daniel_werndl — Fri, 16 Jan 2015 09:50:09 GMT

Thanks for the reply.

Example:

Bob, Karl and John bought Stuff in the year 2013

Susi, Bob and Frank bought Stuff only in 2014

on selecting the Year 2013 & 2014 the ListBox Customer should only list Bob since he is the only one having a purchase in both years

Re: Create Listbox with values debending on AND selection in another Listbox

Anonymous — Fri, 16 Jan 2015 10:03:04 GMT

I guess, instead of having AND mode on year, it should be on Customer.

Re: Create Listbox with values debending on AND selection in another Listbox

daniel_werndl — Fri, 16 Jan 2015 10:15:34 GMT

Of course, on selecting the customer Bob i would get the years 2013+2014. standart.

But thats not the behavior I want.

Re: Create Listbox with values debending on AND selection in another Listbox

daniel_werndl — Mon, 19 Jan 2015 09:09:07 GMT

Found the Solution:

andYear:

LOAD DISTINCT

Name

resident Rechnung;

JOIN (andYear) LOAD Year as customerYear Resident MasterCalendar;

This would Join a Year to every Name resulting in there being no AND to select since the Year is on every Customer.

Changed it to:

andCustomer:

LOAD DISTINCT

Name,

YEAR(Date) as CustomerYear

resident Rechnung;

Expression in the ListBox is no longer needed of course