Join Data - Calendar - sum(value)

[#1]

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);

[rwunderlich]

Take a look at the IntervalMatch script keyword.

-Rob

[#3]

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);

[MindaugasBacius]

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:

[#5]

Hello,

Thank you, but i need the value (sum(price)) in my table because i will use to calculate other KPI.

Best regards

Hamza

[MindaugasBacius · Accepted solution]

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:

[manideep78]

Hi,

Please find the attached application for solution.

Hope this helps!

[#8]

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;

[MindaugasBacius]

If I do understand you correctly in the last load remove Num_File field and you will get the sum only between intervals.

[#10]

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