Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
Use interval match or did you want the logic how to use interval match
Regard
Anand
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)
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
I didn't mentioned that I do need sum in the script.
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