Get each date in period

[eugeniyaromanov]

Hello everyone!

Can't find a solution, may be someone can help me.

I have a table:

PeriodStart	PeriodEnd	Field1	Field2 (with Salary)
01.01.2001	01.02.2001	Q1	110
02.02.2001	01.03.2001	Q1	120
02.03.2001	01.04.2001	Q2	130
02.04.2001	01.05.2001	Q2	140
02.05.2001	01.06.2001	Q3	150
02.06.2001	01.07.2001	Q4	160

I need to get a table like:

Date	Field1	Field3(Salary per Day)
01.01.2001	Q1	3,22
02.01.2001	Q1	3,22
03.01.2001	Q1	3,22
...
02.04.2001	Q2	4,66
03.04.2001	Q2	4,66

And so on..

Periods can be any length, not a whole month, it can be one day, two days or three month.

No need to count weekends or smth, every day in a period is a work day.

So basically we have to do this:

Field2/(PeriodEnd-PeriodStart+1) and somehow to compare it with each date in period.

I believe it has something to do with intervalmatch, but can't figure it out.

Does anyone know what to do?

Thanks in advance!

[sunny_talwar · Accepted solution]

May be this

Table:

LOAD Date(PeriodStart + IterNo() - 1) as Date,

  Field1,

  Field2/(PeriodEnd - PeriodStart + 1) as Field2

While PeriodStart + IterNo() - 1 <= PeriodEnd;

LOAD * INLINE [

    PeriodStart, PeriodEnd, Field1, Field2

    01.01.2001, 01.02.2001, Q1, 110

    02.02.2001, 01.03.2001, Q1, 120

    02.03.2001, 01.04.2001, Q2, 130

    02.04.2001, 01.05.2001, Q2, 140

    02.05.2001, 01.06.2001, Q3, 150

    02.06.2001, 01.07.2001, Q4, 160

];

[eugeniyaromanov]

Hello!

Thanks for help!

I'll try this!

But i figured it out!

If someone needs it:

1. We should create a calendar from a mastercalendar:

CalendarTest:

LOAD

Year(Date_tmp) as YearTest

,Month(Date_tmp) as MonthTest

,Day(Date_tmp) as DayTest 

,date(Date_tmp, 'DD.MM.YYYY')  as DateTest

,MonthName(Date_tmp) as MonthYearTest

  Resident CALENDAR_Work;

2. We should create our table:

Table:

Load * inline

[

PeriodStart, PeriodEnd,Field1,Field2

01.01.2014,01.02.2014,Q1, 100

02.02.2014,01.03.2014,Q1, 110

…

    02.10.2014,01.11.2014,Q4, 190

     02.11.2014,01.12.2014,Q4, 200

     02.12.2014,31.12.2014,Q4, 300

     ];

    3. We create third table:

Table1:

load

PeriodStart, PeriodEnd,Field1,Field2

,Field2/(PeriodEnd-PeriodStart+1) as Field3 // It’s a salary, so we need to count it per day

Resident Table;

DROP Table Table;

RENAME Table Table1 to Table;

IntervalMatch (DateTest) LOAD PeriodStart, PeriodEnd Resident Table;

And here it is!

I have exactly what I need.

[sunny_talwar]

Great, but what I gave did not work for you? I mean the efficiency wise I don't see much difference between the two, but the script I provided seems concise....

[eugeniyaromanov]

Hi Sunny!

I will surely try this! To me it seems easier, than mine.

Problem is I can do only tomorrow, at work.

[eugeniyaromanov]

Hi again!

Something's not right...

Code:

Table:

LOAD Date(PeriodStart + IterNo() - 1) as Date,

  Field1,

  Field2/(PeriodEnd - PeriodStart + 1) as Field2

While PeriodStart + IterNo() - 1 <= PeriodEnd;

LOAD * INLINE [

PeriodStart, PeriodEnd,Field1,Field2

01.01.2014,01.02.2014,Q1, 100

02.02.2014,01.03.2014,Q1, 110

02.03.2014,01.04.2014,Q1, 120

02.04.2014,01.05.2014,Q2, 130

02.05.2014,01.06.2014,Q2, 140

02.06.2014,01.07.2014,Q2, 150

02.07.2014,01.08.2014,Q2, 160

02.08.2014,01.09.2014,Q2, 170

02.09.2014,01.10.2014,Q2, 180

02.10.2014,01.11.2014,Q4, 190

02.11.2014,01.12.2014,Q4, 200

02.12.2014,31.12.2014,Q4, 300

];

From code I get data (I changed year for 2014):

ДатаTest	Field1	Field2
01.01.2014	Q1	3.125
01.01.2014	Q1	3.870967742
01.01.2014	Q1	3.928571429
02.01.2014	Q1	3.125
02.01.2014	Q1	3.870967742
02.01.2014	Q1	3.928571429
03.01.2014	Q1	3.125
03.01.2014	Q1	3.870967742
03.01.2014	Q1	3.928571429
04.01.2014	Q1	3.125
04.01.2014	Q1	3.870967742
04.01.2014	Q1	3.928571429

And the rigth one is the first at each date, so the second and the third on 01.01.2014 for example is counting smth else.

I beliebe it is data from another period. 3.870967742 is from 02.03.2014-01.04.2014 and 3.928571429 is from 02.02.2014-01.03.2014.

And Q1 (for example in my table was fired on 01.04.2014) coundn't be working on 25.06.2014, but the data is:

25.06.2014	Q1	3.125
25.06.2014	Q1	3.870967742
25.06.2014	Q1	3.928571429

As for Q2 (he worked 6 periods), I get 6 different velues from period he couldn't be working:

01.01.2014	Q2	4.333333333
01.01.2014	Q2	4.516129032
01.01.2014	Q2	5
01.01.2014	Q2	5.161290323
01.01.2014	Q2	5.483870968
01.01.2014	Q2	6

[sunny_talwar]

I got this running your script

Is this not right?

[eugeniyaromanov]

Hello!

Yes! It is right!

It should be my mistake, may be because of the calendar I used....

I've tried it on original script and it worked!

I really appreciate your help

Thanks!