Max date for period

[Jack4]

Hello.

I would like to get max Date for each Company in 3 months period.

E.g. company A starting date is 28.11.2019. This is a min Date and I want to find max Date for company A up to 28.01.2020.

In my case it is 13.01.2020. And then I want to replace earlier dates for that company with 13.01.2020. How can I do that?

Thanks.

Original table:

ID	Date	Company	Value
1	28.11.2019	A	12
2	29.11.2019	A	56
3	29.11.2019	A	31
4	08.12.2019	A	23
5	13.01.2020	A	24
6	24.05.2020	A	21
7	28.11.2019	B	32
8	02.12.2019	B	22

Expected result:

ID	Date	Company	Value
1	13.01.2020	A	12
2	13.01.2020	A	56
3	13.01.2020	A	31
4	13.01.2020	A	23
5	13.01.2020	A	24
6	24.05.2020	A	21
7	02.12.2019	B	32
8	02.12.2019	B	22

LOAD * Inline [
ID, Date, Company, Value
1, 28.11.2019, A, 12
2, 29.11.2019, A, 56
3, 29.11.2019, A, 31
4, 08.12.2019, A, 23
5, 13.01.2020, A, 24
6, 24.05.2020, A, 21
7, 28.11.2019, B, 32
8, 02.12.2019, B, 22
];

[tresesco]

Try using expression like:

Date(Max(Total <Company> Date))

[Jack4]

Hello, Tresesco.

I  want to do that in load script.

Also, your expression shows 24.05.2020 for every company, which is not what I am trying to achieve.

[sunny_talwar]

You can try this

Table:
LOAD * INLINE [
    ID, Date, Company, Value
    1, 28.11.2019, A, 12
    2, 29.11.2019, A, 56
    3, 29.11.2019, A, 31
    4, 08.12.2019, A, 23
    5, 13.01.2020, A, 24
    6, 24.05.2020, A, 21
    7, 28.11.2019, B, 32
    8, 02.12.2019, B, 22
];

Left Join (Table)
LOAD Company,
	 Max(Date) as MaxDate
Resident Table
Where Date <= MakeDate(2020, 1, 28)
Group By Company;

FinalTable:
LOAD ID,
	 Date(RangeMax(Date, MaxDate)) as Date,
	 Company,
	 Value
Resident Table;

DROP Table Table;

[Jack4]

Thanks, Sunny. This one is much closer to what I am trying to get. But I can't really use that "Where Date <=" clause.

The thing is, I have lots of different dates and companies in my data. So script should not consist of manual limitations for dates.

Company A may have data for 01.09.2020. In that case I'll need 3 max dates: 13.01.2020, 24.05.2020 and 01.09.2020

[sunny_talwar]

@Jack4 - but you do need to supply the date somehow, right? I mean it can be from another source. But we need to tell the script to find max date before a certain date other wise it will give you the max date for any company.

[Jack4]

@sunny_talwar In my example I want to take minimal Date for a Company (28.11.2019 for company A), then find max Date which can be up to 3 months from minimal (13.01.2020 for company A). Then I want 24.05.2020 to become new minimal Date and so on.

[sunny_talwar]

@Jack4 - oh okay, but what will you do with the new minimal Date?

[Jack4]

@sunny_talwar After 24.05.2020 becomes a new minimal Date I want to look for a bigger date for that Company in next 3 months (up to 24.07.2020). If there is a bigger date, then it becomes a max Date and replaces previous dates (starting from 24.05.2020). If there is no Date bigger than 24.05.2020 and lower than 24.07.2020 then just leave 24.05.2020. And start searching for a new minimal Date bigger than 24.07.2020. For example 01.09.2020 which then becomes a new minimal Date.

Hope I made it clear now.

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD * INLINE [
    ID, Date, Company, Value
    1, 28.11.2019, A, 12
    2, 29.11.2019, A, 56
    3, 29.11.2019, A, 31
    4, 08.12.2019, A, 23
    5, 13.01.2020, A, 24
    6, 24.05.2020, A, 21
    7, 28.06.2020, A, 23
    8, 01.09.2020, A, 45
    9, 30.12.2020, A, 65
    10, 28.11.2019, B, 32
    11, 02.12.2019, B, 22
];

FinalTable:
LOAD *,
	 Date(If(Company = Previous(Company),
	 	If(Date < Alt(Peek('tmpDate'), AddMonths(Date, 3)), Alt(Peek('tmpDate'), AddMonths(Date, 3)), AddMonths(Date, 3)), AddMonths(Date, 3))) as tmpDate
Resident Table
Order By Company, Date;

DROP Table Table;

Left Join (FinalTable)
LOAD Company,
	 tmpDate,
	 Date(Max(Date)) as MaxDate
Resident FinalTable
Group By Company, tmpDate;

DROP Fields Date, tmpDate;
RENAME Field MaxDate to Date;