Sum in between intervals

[MindaugasBacius]

Good morning,

I would like to have sum of sales in between given intervals:

Sales table:

Sales date	Buyer	Value
2016-01-01	A	1
2016-02-01	A	2
2016-02-02	B	3

Interval table:

Buyer	From	To
A	2016-01-01	2016-01-05
A	2016-02-01	2016-02-05
B	2016-01-01	2016-02-29

The result:

Sales date	Buyer	Value	From	To	Sum
2016-01-01	A	1	2016-01-01	2016-01-05	1
2016-02-01	A	2	2016-02-01	2016-02-05	2
2016-02-02	B	3	2016-01-01	2016-02-29	3

In production version the Sales table have few million transactions.

Interval table contains around one thousand rows.

Thank you for your ideas!

[its_anandrjs]

Use interval match or did you want the logic how to use  interval match

Regard

Anand

[jonathandienst]

Heres' the logic

Sales:

LOAD [Sales date],

  Buyer,

  Value,

  ...

Interval:

LOAD Buyer,

  From,

  To

FROM ...

Left Join (Sales)

IntervalMatch([Sales date], Buyer)

LOAD From, To, Buyer

Resident Interval;

DROP Table Interval;

Then create a straight table with dimensions: [Sales date], Buyer, From, To

And expression: Sum(Value)

[its_anandrjs]

Try this way also

Sales_table:
LOAD * Inline [
Sales date,     Buyer,     Value
2016-01-01,     A,   1
2016-02-01,     A,   2
2016-02-02,     B,   3
];

Interval_table:
IntervalMatch(Buyer)
LOAD * Inline [
From, To
2016-01-01,     2016-01-05
2016-02-01,     2016-02-05
2016-01-01,     2016-02-29
];

Regards

Anand

[MindaugasBacius]

I didn't mentioned that I do need sum in the script.

[antoniotiman · Accepted solution]

Hi Mindaugas,

try

Temp:
LOAD [Sales date],
Buyer,
Value
FROM
"https://community.qlik.com/message/1156621"
(html, codepage is 1252, embedded labels, table is @1);

Left Join
LOAD Buyer,
From,
To
FROM
"https://community.qlik.com/message/1156621"
(html, codepage is 1252, embedded labels, table is @2);


Left Join
Load DISTINCT Buyer,[Sales date],
Sum(If([Sales date] >= From and [Sales date] <= To,Value,0)) as Sum
Resident Temp
Group By Buyer,[Sales date];

NoConcatenate
Load *
Resident Temp
Where [Sales date] >= From and [Sales date] <= To;

Drop Table Temp;

Regards,

Antonio