topic Re: Join Data - Calendar - sum(value) in QlikView

Join Data - Calendar - sum(value)

Wed, 26 Apr 2017 09:34:07 GMT

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

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

Re: Join Data - Calendar - sum(value)

rwunderlich — Wed, 26 Apr 2017 11:03:28 GMT

Take a look at the IntervalMatch script keyword.

-Rob

Re: Join Data - Calendar - sum(value)

Wed, 26 Apr 2017 13:09:53 GMT

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

Re: Join Data - Calendar - sum(value)

MindaugasBacius — Wed, 26 Apr 2017 13:46:52 GMT

You will get the idea:

Purchase:

LOAD * INLINE [

Date, Price

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:

Re: Join Data - Calendar - sum(value)

Wed, 26 Apr 2017 13:53:36 GMT

Hello,

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

Best regards

Hamza

Re: Join Data - Calendar - sum(value)

MindaugasBacius — Wed, 26 Apr 2017 13:59:23 GMT

Purchase:

LOAD * INLINE [

Date, Price

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:

Re: Join Data - Calendar - sum(value)

manideep78 — Wed, 26 Apr 2017 14:37:25 GMT

Hi,

Please find the attached application for solution.

Hope this helps!

Re: Join Data - Calendar - sum(value)

Wed, 26 Apr 2017 15:40:11 GMT

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;

Re: Join Data - Calendar - sum(value)

MindaugasBacius — Thu, 27 Apr 2017 05:19:06 GMT

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

Re: Join Data - Calendar - sum(value)

Thu, 27 Apr 2017 06:44:25 GMT

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

Re: Join Data - Calendar - sum(value)

Thu, 27 Apr 2017 11:29:23 GMT

I put the filter in the last load, sorry and Thank you very much.