Re: Sum of Values between 2 dates - Qlik Community

andreip21 · ‎2018-03-19

Hello all,

I have 2 tables:

T1:

Order Timestamp	Sales	Order ID	MKT ID
3/1/2018 0:00	100	2207874236	21598828
3/1/2018 1:00	50	2207874242	35660586
3/1/2018 1:00	90	2207874304	21598828
3/1/2018 12:00	60	2207874292	21598828
3/1/2018 13:00	60	2207874152	21598828
3/1/2018 13:00	70	2207874184	35660586
3/1/2018 18:00	30	2207874214	21598828
3/1/2018 22:00	300	2207874216	35660586
3/1/2018 22:00	170	2207874470	35660586
3/1/2018 22:00	40	2207874956	35660586

and T2 (without the last column, which i want to calculate)

Price Start Date	Price End Date	MKT ID	Sales
3/1/2018 0:00	3/1/2018 11:00	21598828	190
3/1/2018 11:00	3/1/2018 18:00	21598828	120
3/1/2018 18:00	3/1/2018 23:59	21598828	30
3/1/2018 0:00	3/1/2018 9:00	35660586	50
3/1/2018 9:00	3/1/2018 23:59	35660586	580

How do i calculate in the script the last column from T2?

Regards,

Andrei

eliran · ‎2018-03-19

Hi,

What you're looking for is the interval match function, once applied, you will have the ability to take the price from T2 according to MKTID and the orderdate timestamp.

Please refer to Henric's blog about this subject to help you with this subject.

https://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

Regards,

Eliran.

Aurelien_Martinez · ‎2018-03-19

Hi,

You can use intervalmatch function like :

t1:
load 
  Timestamp#([Order Timestamp], 'MM/DD/YYYY hh:mm') as [Order Timestamp],
  Sales,
  [Order ID],
  [MKT ID]
Inline [
Order Timestamp, Sales, Order ID, MKT ID
3/1/2018 0:00, 100, 2207874236, 21598828
3/1/2018 1:00, 50, 2207874242, 35660586
3/1/2018 1:00, 90, 2207874304, 21598828
3/1/2018 12:00, 60, 2207874292, 21598828
3/1/2018 13:00, 60, 2207874152, 21598828
3/1/2018 13:00, 70, 2207874184, 35660586
3/1/2018 18:00, 30, 2207874214, 21598828
3/1/2018 22:00, 300, 2207874216, 35660586
3/1/2018 22:00, 170, 2207874470, 35660586
3/1/2018 22:00, 40, 2207874956, 35660586
];


t2:
Load 
  Timestamp#([Price Start Date], 'MM/DD/YYYY hh:mm') as [Price Start Date],
  Timestamp#([Price End Date], 'MM/DD/YYYY hh:mm') as [Price End Date],
  [MKT ID] 


Inline [
Price Start Date, Price End Date, MKT ID
3/1/2018 0:00, 3/1/2018 11:00, 21598828
3/1/2018 11:00, 3/1/2018 18:00, 21598828
3/1/2018 18:00, 3/1/2018 23:59, 21598828
3/1/2018 0:00, 3/1/2018 9:00, 35660586
3/1/2018 9:00, 3/1/2018 23:59, 35660586
];




Left Join(t2)
IntervalMatch([Order Timestamp])
Load
  [Price Start Date],
  [Price End Date]
Resident t2
;






Left Join(t2)
Load
  [Order Timestamp],
  Sales,
  [MKT ID]
Resident t1
;


Drop Table t1;


tf:
Load
  [Price Start Date],
  [Price End Date],
  [MKT ID],
  Sum(Sales) as Sales
Resident t2
Group By
    [Price Start Date],
  [Price End Date],
  [MKT ID]
;


Drop Table t2;

Help users find answers! Don't forget to mark a solution that worked for you!

shiveshsingh · ‎2018-03-19

Try this

T1:LOAD

Timestamp#([Order Timestamp], 'MM/DD/YYYY hh:mm') as [Order Timestamp],Sales,

[Order ID],

[MKT ID] INLINE [

Order Timestamp, Sales, Order ID, MKT ID

3/1/2018 0:00, 100, 2207874236, 21598828

3/1/2018 1:00, 50, 2207874242, 35660586

3/1/2018 1:00, 90, 2207874304, 21598828

3/1/2018 12:00, 60, 2207874292, 21598828

3/1/2018 13:00, 60, 2207874152, 21598828

3/1/2018 13:00, 70, 2207874184, 35660586

3/1/2018 18:00, 30, 2207874214, 21598828

3/1/2018 22:00, 300, 2207874216, 35660586

3/1/2018 22:00, 170, 2207874470, 35660586

3/1/2018 22:00, 40, 2207874956, 35660586

];

T2:

LOAD Timestamp#([Price Start Date], 'MM/DD/YYYY hh:mm') as [Price Start Date],

Timestamp#([Price End Date], 'MM/DD/YYYY hh:mm') as [Price End Date], [MKT ID] INLINE [

Price Start Date, Price End Date, MKT ID

3/1/2018 0:00, 3/1/2018 11:00, 21598828

3/1/2018 11:00, 3/1/2018 18:00, 21598828

3/1/2018 18:00, 3/1/2018 23:59, 21598828

3/1/2018 0:00, 3/1/2018 9:00, 35660586

3/1/2018 9:00, 3/1/2018 23:59, 35660586

];

left join(T2)

IntervalMatch([Order Timestamp])

load [Price Start Date], [Price End Date]

Resident T2;

left join(T2)

load

Sales, [Order Timestamp],[MKT ID]

Resident T1;

drop table T1;

shiveshsingh · ‎2018-03-19

Or you can use Key to remove synthetic key rather than join,

T1:LOAD

Timestamp#([Order Timestamp], 'MM/DD/YYYY hh:mm') as [Order Timestamp],Sales,

[Order ID],

[MKT ID] INLINE [

Order Timestamp, Sales, Order ID, MKT ID

3/1/2018 0:00, 100, 2207874236, 21598828

3/1/2018 1:00, 50, 2207874242, 35660586

3/1/2018 1:00, 90, 2207874304, 21598828

3/1/2018 12:00, 60, 2207874292, 21598828

3/1/2018 13:00, 60, 2207874152, 21598828

3/1/2018 13:00, 70, 2207874184, 35660586

3/1/2018 18:00, 30, 2207874214, 21598828

3/1/2018 22:00, 300, 2207874216, 35660586

3/1/2018 22:00, 170, 2207874470, 35660586

3/1/2018 22:00, 40, 2207874956, 35660586

];

T2:

LOAD Timestamp#([Price Start Date], 'MM/DD/YYYY hh:mm') as [Price Start Date],

Timestamp#([Price End Date], 'MM/DD/YYYY hh:mm') as [Price End Date], [MKT ID] INLINE [

Price Start Date, Price End Date, MKT ID

3/1/2018 0:00, 3/1/2018 11:00, 21598828

3/1/2018 11:00, 3/1/2018 18:00, 21598828

3/1/2018 18:00, 3/1/2018 23:59, 21598828

3/1/2018 0:00, 3/1/2018 9:00, 35660586

3/1/2018 9:00, 3/1/2018 23:59, 35660586

];

left join(T2)

IntervalMatch([Order Timestamp])

load [Price Start Date], [Price End Date]

Resident T2;

T3:

load [Price Start Date], [Price End Date], [MKT ID],[Order Timestamp],[Order Timestamp]&'|'&[MKT ID] as Key

Resident T2;

drop table T2;

T4:

load

Sales, [Order ID],[Order Timestamp]&'|'&[MKT ID] as Key

Resident T1;

drop table T1;