Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gramqlik
Contributor III
Contributor III

Date from one QVD lookup against range in another

At present, I have an extract QVW with a SQL SELECT that begins with a number of CASE statements checking the "BDate" field against a number of hard-coded date ranges to find the Period value per record.

ExtractData:

SQL
SELECT
CASE

  WHEN CONVERT(VARCHAR(88), a."BDate", 111) BETWEEN '2017/12/31' AND  '2018/01/27' THEN '1'

  WHEN CONVERT(VARCHAR(88), a."BDate", 111) BETWEEN '2018/01/28' AND  '2018/02/24' THEN '2'

  WHEN CONVERT(VARCHAR(88), a."BDate", 111) BETWEEN '2018/02/25' AND  '2018/03/31' THEN '3'

  [... etc ...]

ELSE '0'
END AS Period,

[...lots of other code...]

;

STORE ExtractData INTO $(vQVDpath)\ExtractData.QVD;

DROP TABLE ExtractData;

I also have an Excel file containing a worksheet "ACalendar" that holds 4 years' worth of these date ranges and the Period associated with each range.

Date From --- Date To --- Period

29-Dec-19 --- 25-Jan-20 --- 1

26-Jan-20 --- 22-Feb-20 --- 2

23-Feb-20 --- 28-Mar-20 --- 3

So....

I'm guessing that it will be faster to extract these 3 columns from the ACalendar table in the Excel file, store them in a QVD, and then perform a lookup for the BDate of each record in the ExtractData table against the Date From and Date To in the ACalendar QVD to get the relevant Period.

My question is, how do I write the lookup to replace the old CASE statements? How do I refer to the fields in the ACalendar QVD within the script for the ExtractData QVD? Using a RESIDENT Load? And would this be a use for IntervalMatch, or would it just be an IF statement and a JOIN ?

Thanks.

3 Replies
gramqlik
Contributor III
Contributor III
Author

Whilst awaiting some input, I've tested loading the calendar .xlsx file and storing into a QVD (on a separate script tab):

ACalendar:

LOAD

    [Date From],

    [Date To],

    [Period]

FROM

$(vResources)\Acalendar.xlsx

(ooxml, embedded labels, table is [Calendar])

WHERE LEN(TRIM([Date From])>0);

STORE ACalendar INTO $(vQVDpath)\ACalendar.QVD;

No problem so far!

I then had a scout about for how to use IntervalMatch with date ranges.

Presumably, I need to call that (Resident) ACalendar table ahead of my ExtractData table, and then join the two on an interval match.

So something like:

DateRanges:

LOAD *

RESIDENT AccountsCalendar

;

ExtractData:

LOAD * ;

SQL
SELECT 

[...whole SQL SELECT code, excluding the old CASE statements...]

FROM abc

WHERE xyz

;

INNER JOIN (DateRanges)

IntervalMatch(ExtractData.SQLDateField)

LOAD

    [Date From],

    [Date To],

    [Period]

RESIDENT DateRanges;

STORE ExtractData INTO $(vQVDpath)\ExtractData.QVD;

DROP TABLE ExtractData;

DROP TABLE DateRanges

Not entirely sure on this, so any help is appreciated!

RadovanOresky
Partner Ambassador
Partner Ambassador

Hi, yes this is a standard case for using IntervalMatch().

The solution that you started to implement is going the right way.

I usually use LEFT JOIN instead of Inner, just because you might have more keys in your ExtractData table than in the AccountsCalendar and you would not want to lose them.

Plus, I would do one more LEFT JOIN after the IntervalMatch statement in order to get the [Period] field into the ExtractData table. So I think it should look something like this (after you pre-loaded the DateRanges a ExtractData tables):

left join (ExtractData) intervalmatch (ExtractData.SQLDateField) load DISTINCT [Date From], [Date To] resident DateRanges;

left join (ExtractData) load DISTINCT [Date From], [Date To], [Period] resident DateRanges;


drop table DateRanges;

Hope this helps.

Radovan

gramqlik
Contributor III
Contributor III
Author

Thanks Radovan, I'll give this a try.

One thing - when you say:

I usually use LEFT JOIN instead of Inner, just because you might have more keys in your ExtractData table than in the AccountsCalendar and you would not want to lose them.

In my case, I think I do need an INNER JOIN - I definitely don't want to include anything in the results that falls outside of the date ranges in the AccountsCalendar, which currently contains Period values from 2015 to 2020. The whole task is to speed up the QVW and reduce the qty of data returned, where possible.

The old CASE statements had hard-coded ranges for 2017 and 2018 only, and there are also WHERE conditions in the SQL to limit the data so that only those with a SQLDateField value between '2017/01/01' AND '2018/12/31' are included.