CODEWITHSUNDEEP
chartsqlikview
JLMullinix
JLMullinix

Reputation: 1

Qlikview - Data between dates; filter out data past or future data depending on selected date

I've seen threads where the document has Start Date and End Date "widgets" where users type in their dates, however, I'm looking for a dynamic solution, for example on the table below, when I select a date, say "1/1/2004", I only want to see active players (this would exclude Michael Jordan only).

Jersey# Name RookieYr RetirementYr Average PPG 23 Michael Jordan 1/1/1984 1/1/2003 24 33 Scotty Pippen 1/1/1987 1/1/2008 15 1 Derrick Rose 1/1/2008 1/1/9999 16 25 Vince Carter 1/1/1998 1/1/9999 18

Upvotes: 0

Views: 1512

Answers (2)

The Budac
The Budac

Reputation: 1633

Here's the interval match for your data. You'll can obviously create your calendar however you want.

STATS:
load * inline [
Jersey#, Name, RookieYr, RetirementYr, Average, PPG 
23, Michael Jordan, 1/1/1984, 1/1/2003, 24 
33, Scotty Pippen, 1/1/1987, 1/1/2008, 15 
1, Derrick Rose, 1/1/2008, 1/1/9999, 16 
25, Vince Carter, 1/1/1998, 1/1/9999, 18
];

let zDateMin=37000;
let zDateMax=40000;

DATES:
LOAD
    Date($(zDateMin) + IterNo() - 1) as [DATE],
    year( Date($(zDateMin) + IterNo() - 1)) as YEAR,
    month( Date($(zDateMin) + IterNo() - 1)) as MONTH
AUTOGENERATE 1
WHILE $(zDateMin)+IterNo()-1<= $(zDateMax);

INTERVAL:   
IntervalMatch (DATE) load RookieYr, RetirementYr resident STATS;

left join (DATES) load * resident INTERVAL; drop table INTERVAL;

There's not much to it you need to load 2 tables one with the start and end dates and one with the calendar dates then you interval match the date field to the start and end field and from there it will work the last join is just to tidy up a bit.

The result of all of that is this ctrl-t. Don't worry about the Syn key it is required to maintain the interval matching.

Don't fear the SynKey

Then you can have something like this.

01/01/2004

Derrick Rose is also excluded since he had not started by 1/1/2004

Upvotes: 0

Rob Wunderlich
Rob Wunderlich

Reputation: 101

The most flexible way is to IntervalMatch the RookieYr * RetireYr dates into a table of all dates. See http://qlikviewcookbook.com/recipes/download-info/count-days-in-a-transaction-using-intervalmatch/ for a complete example.

Upvotes: 1

Related Questions