topic Re: Help with IntervalMatch Join in QlikView

Help with IntervalMatch Join

Tue, 04 Mar 2014 11:18:41 GMT

Hi guys,

I've got an issue trying to join 2 tables together using interval match.

My first table holds Customer Account Numbers, A Discount Code and Start Date and an End Date for that Discount, for example;

CustKeyGroup	Pricing Type Group	Start Date Group	End Date Group
50893-000	SE1	09/01/2013	31/03/2013
50893-000	SE1	01/04/2013	31/05/2013
50893-000	SE1	01/06/2013	31/07/2013
50893-000	SE1	01/08/2013	31/08/2013
50893-000	SE1	01/09/2013	30/09/2013
50893-000	SE1	01/10/2013	31/03/2014

My Second table holds Customer Account Numbers, An invoice Date and a sum of the Daily Turnover, for example;

DailyCustKey	Daily Invoice Date	DailySalesTemp
50893-000	24/02/2014	27.57
50893-000	05/02/2014	372.92
50893-000	27/01/2014	15.45
50893-000	20/01/2014	336.58
50893-000	10/01/2014	11.11
50893-000	04/12/2013	106.18
50893-000	26/11/2013	122.82
50893-000	11/11/2013	48.44
50893-000	08/11/2013	11.96
50893-000	24/10/2013	33.02
50893-000	27/09/2013	193.22
50893-000	19/09/2013	306.28
50893-000	10/09/2013	343.87
50893-000	03/09/2013	183.56
50893-000	02/09/2013	77.5
50893-000	30/08/2013	54.42

I'm looking for a way to be able to calculate the Sales By Customer, within each of the Periods. I'm pretty sure i need to use intervalmatch, however, i also need to ensure that it's joining by Customer Account. For the purposes of the example, i've only included a single customer account, but there will be more within the report.

Hopefully that makes sense.

Regards,

Nick

Re: Help with IntervalMatch Join

alexandros17 — Tue, 04 Mar 2014 13:06:06 GMT

T1:

LOAD * Inline [

CustKeyGroup, PricingTypeGroup, StartDateGroup, EndDateGroup

'50893-000', SE1, 09/01/2013, 31/03/2013

'50893-000', SE1, 01/04/2013, 31/05/2013

'50893-000', SE1, 01/06/2013, 31/07/2013

'50893-000', SE1, 01/08/2013, 31/08/2013

'50893-000', SE1, 01/09/2013, 30/09/2013

'50893-111', SE1, 01/09/2013, 30/09/2013

'50893-000', SE1, 01/10/2013, 31/03/2014

];

T2:

LOAD * Inline [

CustKeyGroup, DailyInvoiceDate, DailySalesTemp

'50893-000', 24/02/2014, 27.57

'50893-000', 05/02/2014, 372.92

'50893-000', 27/01/2014, 15.45

'50893-000', 20/01/2014, 336.58

'50893-000', 10/01/2014, 11.11

'50893-000', 04/12/2013, 106.18

'50893-000', 26/11/2013, 122.82

'50893-000', 11/11/2013, 48.44

'50893-000', 08/11/2013, 11.96

'50893-000', 24/10/2013, 33.02

'50893-000', 27/09/2013, 193.22

'50893-000', 19/09/2013, 306.28

'50893-111', 19/09/2013, 999.99

'50893-000', 10/09/2013, 343.87

'50893-000', 03/09/2013, 183.56

'50893-000', 02/09/2013, 77.5

'50893-000', 30/08/2013, 54.42

];

Left Join

IMC:

IntervalMatch ( [DailyInvoiceDate] ) Load [StartDateGroup], [EndDateGroup] Resident T1;

Re: Help with IntervalMatch Join

Tue, 04 Mar 2014 13:25:36 GMT

Thanks Alessandro,

I think your solution works, however, when i have 33000 lines in Table 1 and over 1mil in Table 2, it causes it to crash on reload. Is there another solution that could have a lower overhead?

Regards,

Nick

Re: Help with IntervalMatch Join

alexandros17 — Tue, 04 Mar 2014 13:28:41 GMT

The problem is due to lack of memory in your computer so if you cannot load table2 the only idea is to split it in period and load periods separately ....

Let me know

Re: Help with IntervalMatch Join

hic — Tue, 04 Mar 2014 13:34:35 GMT

If you don't join them, QlikView will probably use less memory. And there is really no reason to join them.

See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

HIC