Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have two tables : calendar and TABLE_A
In my calendar table, for each date i have field_A
--> to Monday - Friday field_A= 11
--> saturday field_A= 4
date_field field_A
01/03/2017 11
02/03/2017 11
03/03/2017 11
04/03/2017 4
05/03/2017 0
06/03/2017 11
07/03/2017 11
In TABLE_A, i have 2 dates (begin_Date and End_Date).
begin_Date End_Date
01/03/2017 01/03/2017
01/03/2017 01/03/2017
01/03/2017 01/03/2017
I want to sum field_A if date_field > begin_Date and date_field < End_Date
Result :
begin_Date End_Date field_A
01/03/2017 04/03/2017 22
03/03/2017 06/03/2017 4
03/03/2017 08/03/2017 26
SomeOne can help me ?
Thank you Very much.
Calendar:
LOAD
date_field,
Field_A
RESIDENT Calendar;
TFA_DATA:
LOAD ID,
begin_Date,
End_Date
FROM
FileMars.csv
(txt, utf8, embedded labels, delimiter is ';', msq, header is 7 lines);
Purchase:
LOAD * INLINE [
Date, Price
2015-01-01, 1
2015-01-01, 1
2015-01-03, 2
2015-01-04, 3
2015-01-02, 2
];
Intervals:
LOAD * INLINE [
From, To
2015-01-01, 2015-01-03
2015-01-04, 2015-01-06
];
left Join (Intervals)
IntervalMatch (Date) LOAD * Resident Intervals;
left Join (Intervals)
LOAD *
Resident Purchase;
DROP Table Purchase;
NoConcatenate
tmp:
LOAD From
,To
,Sum(Price)
Resident Intervals
Group By From, To;
DROP Table Intervals;
Result:
Take a look at the IntervalMatch script keyword.
-Rob
Hello Rob,
I tried to use IntervalMarch, but i dont know how.
Thank you for your help.
Calendar:
LOAD
date_field,
Field_A
RESIDENT Calendar;
TFA_DATA:
LOAD ID,
begin_Date,
End_Date
FROM
FileMars.csv
(txt, utf8, embedded labels, delimiter is ';', msq, header is 7 lines);
You will get the idea:
Purchase:
LOAD * INLINE [
Date, Price
2015-01-01, 1
2015-01-01, 1
2015-01-03, 2
2015-01-04, 3
2015-01-02, 2
];
Intervals:
LOAD * INLINE [
From, To
2015-01-01, 2015-01-03
2015-01-04, 2015-01-06
];
IntervalMatch (Date) LOAD * Resident Intervals;
Result:
Hello,
Thank you, but i need the value (sum(price)) in my table because i will use to calculate other KPI.
Best regards
Hamza
Purchase:
LOAD * INLINE [
Date, Price
2015-01-01, 1
2015-01-01, 1
2015-01-03, 2
2015-01-04, 3
2015-01-02, 2
];
Intervals:
LOAD * INLINE [
From, To
2015-01-01, 2015-01-03
2015-01-04, 2015-01-06
];
left Join (Intervals)
IntervalMatch (Date) LOAD * Resident Intervals;
left Join (Intervals)
LOAD *
Resident Purchase;
DROP Table Purchase;
NoConcatenate
tmp:
LOAD From
,To
,Sum(Price)
Resident Intervals
Group By From, To;
DROP Table Intervals;
Result:
Hi,
Please find the attached application for solution.
Hope this helps!
My version it's not working and i wanted to know if can i exclude date in the intervalmatch, because i want to sum only date_field > begin_Date and date_field < End_Date
Calendar:
LOAD * INLINE [
Date, Delay
01/03/2017, 11
02/03/2017, 11
03/03/2017, 11
04/03/2017, 4
05/03/2017, 0
06/03/2017, 11
07/03/2017, 11
08/03/2017, 11
09/03/2017, 11
10/03/2017, 11
11/03/2017, 4
12/03/2017, 0
13/03/2017, 11
14/03/2017, 11
15/03/2017, 11
16/03/2017, 11
17/03/2017, 11
18/03/2017, 4
19/03/2017, 0
20/03/2017, 11
21/03/2017, 11
22/03/2017, 11
23/03/2017, 11
24/03/2017, 11
25/03/2017, 4
26/03/2017, 0
27/03/2017, 11
28/03/2017, 11
29/03/2017, 11
30/03/2017, 11
31/03/2017, 11
];
File :
LOAD * INLINE [
Num_File, Begin, End
1053774 ,01/03/2017 ,01/03/2017
1099003 ,01/03/2017 ,02/03/2017
1267326 ,01/03/2017 ,28/03/2017
1277476 ,01/03/2017 ,03/03/2017
1309707 ,10/03/2017 ,15/03/2017
1309802 ,10/03/2017 ,13/03/2017
1312444 ,13/03/2017 ,17/03/2017
1322585 ,16/03/2017 ,29/03/2017
1329689 ,17/03/2017 ,28/03/2017
];
left Join (File)
IntervalMatch (Date) LOAD * Resident File;
left Join (File)
LOAD *
Resident Calendar;
DROP Table Calendar;
NoConcatenate
tmp:
LOAD
Num_File,
Begin,
End,
Sum(Delay)
Resident File
Group By Num_File, Begin, End;
DROP Table File;
If I do understand you correctly in the last load remove Num_File field and you will get the sum only between intervals.
Ok but do you know if can i exclude dates (begin and End) in the intervalmatch, because i want to sum only date> begin and date < End
Best regards
Hamza