topic Comparing a date value to a set of date ranges in QlikView

Comparing a date value to a set of date ranges

Wed, 25 Nov 2020 16:16:04 GMT

Hi - I'm a relatively novice Qlikview user. I have a set of transactions and I need to compare those transactions to a range of dates to determine if they occur within any of those ranges for an individual.

Person 1:

Start 1 -End 1

Start 2 - End 2

Start 3-End 3

Person 1 transactions:

Trans date 1

trans dates 2

trans date 3

trans date 4

etc. The idea is the identify by person any transactions which are outside of the date ranges. I was hoping someone could suggest an approach to tackle the problem or point me to an example, I might model. I've done basic dashboards, not am just really learning set analysis functionalities. I'd appreciate any advice.

Re: Comparing a date value to a set of date ranges

ecolomer — Sun, 02 Apr 2017 02:52:21 GMT

Use IntervalMatch function

See this:

Interval Match Feature/Function

IntervalMatch and Slowly Changing Dimensions

Re: Comparing a date value to a set of date ranges

Kushal_Chawda — Sun, 02 Apr 2017 07:55:37 GMT

Range: // Table that contains StartDate & EndDate for each person

MAPPING LOAD

StartDate+iterno()-1 & Person as Dates,

1 as Flag

FROM Source

while StartDate+iterno()-1 <= EndDate;

Transaction: //Transaction table

LOAD [Trans Date],

Person,

applymap('Range',[Trans Date]&Person,'NA') as Flag

FROM TransTable;

Now, where ever Flag is "NA" in transaction table, that means that person has transaction out of range.