Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Help with IntervalMatch Join

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-000SE109/01/201331/03/2013
50893-000SE101/04/201331/05/2013
50893-000SE101/06/201331/07/2013
50893-000SE101/08/201331/08/2013
50893-000SE101/09/201330/09/2013
50893-000SE101/10/201331/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-00024/02/201427.57
50893-00005/02/2014372.92
50893-00027/01/201415.45
50893-00020/01/2014336.58
50893-00010/01/201411.11
50893-00004/12/2013106.18
50893-00026/11/2013122.82
50893-00011/11/201348.44
50893-00008/11/201311.96
50893-00024/10/201333.02
50893-00027/09/2013193.22
50893-00019/09/2013306.28
50893-00010/09/2013343.87
50893-00003/09/2013183.56
50893-00002/09/201377.5
50893-00030/08/201354.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

1 Solution

Accepted Solutions

Re: Help with IntervalMatch Join

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

View solution in original post

4 Replies

Re: Help with IntervalMatch Join

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;

Not applicable

Re: Help with IntervalMatch Join

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

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

View solution in original post

Re: Help with IntervalMatch Join

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