Difficult ApplyMap() or possible alternative?

[#1]

Hi,

I'm developing a load script where I am trying to map an ID from a look up table into my main table I'm loading. I would normally use ApplyMap(), however the look up logic requires a validation of a record date (main table) between two date fields in the look up table (ValidFrom and ValidTo) as well as a look up against BranchID and ServiceID (but ServiceID in the LookUpTable may be null in which case, match only on the other fields).

LookUpTable:

ValidFrom (date),

ValidTo (date),

BranchID,

ServiceID

LookUpID

MainTable:

RecordDate,

BranchID,

ServiceID

etc...

I'm hoping to avoid push back onto the IT team to write a stored procedure to handle this outside of the QV loading script. If anyone has ideas I'd be grateful. Thoughts I've had are that perhaps I could solve it with one of:

• IntervalMatch,
• A sub routine (using variables that I then feed back into the main table though this seems convoluted and probably won't be robust),
• ...or I could look at Expressor to handle this?
• (Final thought is that I've missed something simple!)

All involve using a composite key to make the match, but I'm struggling with how to handle all the conditional elements as well.

Many thanks in advance,

Dave

[Gysbert_Wassenaar · Accepted solution]

You could use the intervalmatch function to link the tables correctly.

LookUpTable:

ValidFrom (date),

ValidTo (date),

LookUpID,

BranchID&'|'&ServiceID as Key

from ...etc

MainTable:

RecordDate,

BranchID,

ServiceID,

BranchID&'|'&ServiceID as Key

etc...

LinkTable:

intervalmatch(RecordDate,Key)

load ValidFrom, ValidTo, Key resident LoopUpTable;

If you're interested in more options you can take a look at this document: IntervalMatch and Slowly Changing Dimensions

[#3]

Try with IntervalMatch (Extended Syntax) and please find the below from Help file:

IntervalMatch (Extended Syntax)

The extended IntervalMatch prefix is used to create a table matching discrete numeric values to one or more numeric intervals, while at the same time matching the values of one or several additional keys.

This is a very powerful and flexible feature that can be used for linking transactions with dimensions that are changing over time: Slowly changing dimensions.

The IntervalMatch prefix must be placed before a Load or Select (SQL) statement that loads the intervals. The table containing the discrete data points and the additional keys must already have been loaded into QlikView before the statement with the IntervalMatch prefix. The prefix transforms the loaded table of intervals and keys to a table that contains an additional column: the discrete numeric data points. It also expands the number of records so that the new table has one record per possible combination of discrete data point, interval and value of the key field(s).

The syntax is:

intervalmatch (matchfield,keyfield1 [ , keyfield2, ... keyfield5 ] ) (loadstatement | selectstatement )

matchfield is the field containing the discrete numeric values to be linked to intervals.

keyfield(s) are fields that contain the additional attributes that are to be matched in the transformation.

loadstatement or selectstatement must result in a table where the first two fields contain the lower and upper limits of each interval and the third and any subsequent fields contain the keyfield(s) present in the IntervalMatch statement. The intervals are always closed, i.e. the end points are included in the interval. Non-numeric limits render the interval to be disregarded (undefined).

In order to avoid undefined interval limits being disregarded, it may be necessary to allow NULL values to map to other fields that constitute the lower or upper limits to the interval. This can be handled by the NullAsValue statement or by an explicit test that replaces NULLs with a numeric value well before or after any of the discrete numeric data points.

Example:

Inner Join IntervalMatch (Date,Key) LOAD FirstDate, LastDate, Key resident Key;

[#4]

Thanks! Using the LinkTable worked really well. Also, the IntervalMatch document you linked to is really useful.

[#5]

Thanks for taking the time to explain IntervalMatch in detail, I hadn't realised how powerful it is!