topic Re: Should I use Intervalmatch in QlikView

Should I use Intervalmatch

jcampbell474 — Fri, 05 Aug 2016 01:03:21 GMT

I have a table with three fields. Store #, open date, and close date. I have another table with dates. Dates are in the format 201601, 201602, etc...

I need to create a column in the first table that indicates if the store is open or closed, for all of the open and close dates in the other table. They can have records after they've closed, so new records indicating the store is closed will not be necessary. I've been reading up on Intervalmatch, but never used it and not sure how to apply it. If it is, how should I apply it? If not, is there a better method??

Thanks in advance!

Re: Should I use Intervalmatch

sunny_talwar — Fri, 05 Aug 2016 01:07:03 GMT

IntervalMatch can be used or you can use While statement to create a single date from open and close date. Both options are resource extensive, but can work in this situation

Re: Should I use Intervalmatch

Clever_Anjos — Fri, 05 Aug 2016 02:04:24 GMT

You can use IntervalMatch

I´ve created a script, so you can study a possible technique

OpeningGrid: // Generating a list of stores with open/close dates
LOAD
  Store,
  OpenDate,
  Date(OpenDate + Floor(Rand()*10)) as CloseDate;
LOAD
  Pick(1+Mod(RecNo(),3),'A','B','C') as Store,
  Date(Floor(Today() - Rand()*100)) as OpenDate
AutoGenerate 50;

OtherTable: // your "another table"
LOAD
  Pick(1+Mod(RecNo(),3),'A','B','C') as Store,
  Date(Floor(Today() - Rand()*100)) as Date
AutoGenerate 50;

Left Join(OtherTable) IntervalMatch(Date,Store) // Date is related to which Open/Close Date?
LOAD
  OpenDate,
  CloseDate,
  Store
Resident OpeningGrid;
Drop Table OpeningGrid;
Left Join(OtherTable) // Calculating a flag to know if the store was open
Load
  Store,
  Date,
  If(IsNull(OpenDate),'N','Y') as [Flag Open]
Resident OtherTable;

Re: Should I use Intervalmatch

jcampbell474 — Fri, 05 Aug 2016 12:45:54 GMT

Sunny and Clever, I really appreciate your assistance.

Clever, thank you for the script - I'm just not sure how to use it with my resident tables. Upon further inspection, I think my scenario description is different. I have a Fact table and a Store table. Joined by ID. The Fact table has MonthTimeKey (201601, 201602, etc...) for the transaction date. The Store table has OpenDate and CloseDate.

How can I create a column in the Fact table that indicates if the store was open or closed at the time of the transaction?

Something like:

if(MonthTimeKey >= OpenDate and Len(CloseDate) < 1, 'Open', if(MonthTimeKey >= OpenDate and MonthTimeKey <= CloseDate,'Open','Closed')) as StoreStatus //If a close date is not present, the store is currently open.

Again, I really appreciate your help. My apologies for the inaccurate description.

Re: Should I use Intervalmatch

Clever_Anjos — Fri, 05 Aug 2016 12:48:08 GMT

Can you post a sample of your app?

Preparing examples for Upload - Reduction and Data Scrambling

Re: Should I use Intervalmatch

Clever_Anjos — Fri, 05 Aug 2016 12:51:05 GMT

Another doubt...

MonthTimeKey is a month right?

Let´s get the 201601 month.

What if the store opened on Jan/05th and closed Jan/10th?

It must counted as opened or not-opened?

Re: Should I use Intervalmatch

jcampbell474 — Fri, 05 Aug 2016 13:35:21 GMT

Clever, thank you. The app is loaded via Binary with many tables, so it would be quite a chore to make it a manageable size. So, I created an app that I think represents the issue(s) I'm having. It is attached.

Objective: Using the MonthTimeKey of the transaction in the Store table, list in a column if the Store was Open or Closed during the month of the transaction. The Date table lists Stores and their Open/Closed dates.

Thanks you again!

Re: Should I use Intervalmatch

Clever_Anjos — Fri, 05 Aug 2016 13:54:32 GMT

Please try this after at the end of your script;

NewDate:
NoConcatenate LOAD
  Store#,
  OpenDate,
  Alt(CloseDate,Year(Today())*100+Month(Today())) as CloseDate
Resident Date;
Drop Table Date;

Left Join(Store) IntervalMatch(MonthTimeKey,Store#) // Date is related to which Open/Close Date? 
LOAD 
  OpenDate, 
  CloseDate, 
  Store# 
Resident NewDate;
Drop Table NewDate;
 Left Join(Store) // Calculating a flag to know if the store was open 
Load 
  Store#, 
  MonthTimeKey, 
  If(IsNull(OpenDate),'N','Y') as [Flag Open] 
Resident Store;

Re: Should I use Intervalmatch

jcampbell474 — Fri, 05 Aug 2016 20:10:21 GMT

Clever, to answer your question. If a store opened on 1/5 and closed on 1/10...it would be considered as Open.

Now, to the script. The example works perfect, but I can't get it to work in my test app. Here is the script I'm using (w/TestFieldNames). I'm getting an error saying that OpenDateYrMth can't be found (in the Flag Open expression). I can't figure out why...

NewDate:

NoConcatenate LOAD

AgtID,

OpenDateYrMth,

CloseDateYrMth

Resident AgentLookup;

Left Join (Data) IntervalMatch(MonthTimeKey,AgtID) // Date is related to which Open/Close Date?

LOAD

AgtID,

OpenDateYrMth,

CloseDateYrMth

Resident NewDate;

Drop Table NewDate;

Left Join(Data) // Calculating a flag to know if the store was open

Load

AgtID,

MonthTimeKey,

If(IsNull(OpenDateYrMth),'N','Y') as [Flag Open]

Resident Data;

Thank you again for the help!

Re: Should I use Intervalmatch

sunny_talwar — Fri, 05 Aug 2016 20:13:19 GMT

I think you got the order of AgtID wrong in the intervalMatch load. AgtID cannot come before the two dates:

Left Join (Data) IntervalMatch(MonthTimeKey,AgtID) // Date is related to which Open/Close Date?

LOAD OpenDateYrMth,

CloseDateYrMth,

AgtID

Resident NewDate;

Re: Should I use Intervalmatch

Clever_Anjos — Fri, 05 Aug 2016 20:18:44 GMT

Left Join (Data) IntervalMatch(MonthTimeKey,AgtID) // Date is related to which Open/Close Date?

LOAD

OpenDateYrMth,

CloseDateYrMth,

AgtID

Resident NewDate;

Re: Should I use Intervalmatch

jcampbell474 — Fri, 05 Aug 2016 23:42:09 GMT

Thanks, Sunny and Clever. That did the trick - no more error! I spent a couple of hours troubleshooting it, only to find out it was the order of the fields. I reckon 🙂

So now it doesn't produce any errors, but it never finishes loading. I'm loading a little more than 22-million records. All of the data loads in 00:01:21, then it just hangs. Memory on the server steadily increases. It just climbed to ~90gb, so I killed the process.

I thought interval match would be the most efficient approach from a system resource perspective. Could the increased resource usage be due to how I have it integrated into my model? Should I try a different method?

Thanks!

Re: Should I use Intervalmatch

sunny_talwar — Sat, 06 Aug 2016 10:41:01 GMT

Jason -

Why don't you not join the Interval Match table? As per HIC the synethic key that is created by keeping the Interval Match table separate isn't a bad thing. Read here: IntervalMatch‌

Extract from the above link:

Not sure how you are using the Flag, but I guess you won't able to create that flag unless you have them in one table. May be use ApplyMap or Lookup to do that.

Re: Should I use Intervalmatch

jcampbell474 — Mon, 08 Aug 2016 12:57:33 GMT

Thank you, Sunny. Henric's post initially made me think that IntervalMatch is what I need to use. I just couldn't figure out how to implement it. Here's my script:

Data2:

Load

AgtID,

MonthTimeKey as Datekey

Resident Data;

Intervals:

LOAD * INLINE [

From, To, Status

198001, 201608, Active];

IntervalMatch:

IntervalMatch (Datekey)

Load Distinct

From,

Resident Intervals;

Do I need need to create each interval in the Intervals table?

Thanks!

Re: Should I use Intervalmatch

sunny_talwar — Mon, 08 Aug 2016 15:46:47 GMT

What you have above looks right, are you running into errors?

Re: Should I use Intervalmatch

jcampbell474 — Mon, 08 Aug 2016 15:52:04 GMT

Not getting any errors - just takes awhile to load/process, even with a limited load of 5000 records. I go to validate the results and the closed date status isn't correct.

Looks like it works for one date. I.e., Active date. Not sure how to look at both the Open and Closed date. (Add a record to the Intervals table for InActive?)