Filter two unrelated date fields with one list Box

Tue, 05 Apr 2016 15:18:49 GMT

Hi Qlickers,

I'm new to Qlikview and this is my first attempt to get involved with the community. I would like to find out about a rather general issue.

What I'm looking for is a way to select two unrelated columns (Date1 &Date210) with one and the same list box (year and month), as shown in the extract below.

So if I choose March 2016 in the upper listbox, everything else should be cut to not only to Date1 OR Date210, but both at the same time.

I have been screening other community threads that seemed to discuss similar issues but none of them really got me further.

Thank you already in advance.

Re: Filter two unrelated date fields with one list Box

stevedark — Tue, 05 Apr 2016 17:06:28 GMT

Hi Yomula,

Generally the best way to deal with anything like this is in the load script and the data model. So, a way you can approach this, if you have a unique ID on each row, is:

MainData:

LOAD

ID,

Date1,

Date210

FROM SourceFile.qvd (qvd);

CombinedDates:

LOAD

ID,

'1' as [Date Type],

Date1 as [Generic Date],

Month(Date1) as Month,

Year(Date1) as Year,

Date(MonthStart(Date1), 'MMM-YY') as [Month Year]

RESIDENT MainData;

CombinedDates:

LOAD

ID,

'210' as [Date Type],

Date210 as [Generic Date],

Month(Date210) as Month,

Year(Date210) as Year,

Date(MonthStart(Date210), 'MMM-YY') as [Month Year]

RESIDENT MainData;

Now, when you build list boxes on the fields in the CombinedDates table and select a date it will pick any row from main data where one or the other date is in the selected range.

The creation of CombinedDates can include as many dates as you like. It is crucial that you have exactly the same fields in each instance of the CombinedDates table (in fact, creating it in a loop would be better - but that is a different thread).

If you had a lot of dates to combine you may chose to create a simpler CombinedDates table with just the Date field in and then have a separate calendar table (you will find lots of posts on creating these).

Another approach altogether would be to have a data island with your date table in and control everything else in Set Analysis. This is very flexible, but the code can get quite tricky quite quickly and the selections can end up counter intuitive. I would aim to do things in the load script and data model every time...

Hope that makes sense and is of help.

Steve

topic Filter two unrelated date fields with one list Box in QlikView

Filter two unrelated date fields with one list Box

Re: Filter two unrelated date fields with one list Box