topic Re: Max date for period in QlikView

Max date for period

Jack4 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Max date for period

tresB — Wed, 23 Sep 2020 12:14:02 GMT

Try using expression like:

Date(Max(Total <Company> Date))

Re: Max date for period

Jack4 — Wed, 23 Sep 2020 12:34:48 GMT

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.

Re: Max date for period

sunny_talwar — Wed, 23 Sep 2020 12:44:27 GMT

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;

Re: Max date for period

Jack4 — Wed, 23 Sep 2020 13:03:41 GMT

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

Re: Max date for period

sunny_talwar — Wed, 23 Sep 2020 13:07:22 GMT

@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.

Re: Max date for period

Jack4 — Wed, 23 Sep 2020 13:14:30 GMT

@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.

Re: Max date for period

sunny_talwar — Wed, 23 Sep 2020 13:23:05 GMT

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

Re: Max date for period

Jack4 — Wed, 23 Sep 2020 13:37:01 GMT

@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.

Re: Max date for period

sunny_talwar — Wed, 23 Sep 2020 13:54:09 GMT

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;

Re: Max date for period

Jack4 — Wed, 23 Sep 2020 14:36:36 GMT

@sunny_talwar Thanks a lot, Sunny! Works like a charm.