topic Re: pull max(Date) from each month in QlikView

pull max(Date) from each month

jyothish8807 — Wed, 30 Sep 2015 14:05:05 GMT

Hello All,

I need a small help from you regarding a logic.

I am trying to write a where clause to pull the max date for each month in a table.

Suppose i have 10 dates in month jan and 12 dates for month feb, so i want to pull only the max date for jan and feb using a where clause.

i have only one date field and rest are data fields.

Date	Daily
21/9/2015	1
5/9/2015	2
12/9/2015	3
1/10/2015	4
5/10/2015	5

So in out put i want 21/9/2015 and 5/10/2015 to be pulled using a where clause.

Thanks

Re: pull max(Date) from each month

Kushal_Chawda — Wed, 30 Sep 2015 14:14:39 GMT

try like this

//Create MonthYear from Date & calculate Max date by MonthYear

Data:

LOAD *,

monthname(Date) as MonthYear;

LOAD date(MakeDate(2015)+IterNo()-1,'DD-MM-YYYY') as Date,

ceil(Rand()*100+1000) as Amount

AutoGenerate(1)

While MakeDate(2015)+IterNo()-1<=Today();

Inner Join

LOAD MonthYear,

date(max(Date),'DD-MM-YYYY') as Date

Resident Data

Group by MonthYear;

Re: pull max(Date) from each month

Kushal_Chawda — Wed, 30 Sep 2015 14:19:12 GMT

Want to load max date while loading data from source?

Re: pull max(Date) from each month

sunny_talwar — Wed, 30 Sep 2015 14:28:01 GMT

May be this:

Table:

LOAD Date,

Daily,

MonthName(Date) as MonthYear;

LOAD * Inline [

Date, Daily

21/9/2015, 1

5/9/2015, 2

12/9/2015, 3

1/10/2015, 4

5/10/2015, 5

];

Join(Table)

LOAD MonthYear,

Max(Date) as Date,

1 as Flag

Resident Table

Group By MonthYear;

ExtractedData:

LOAD Date as Date1

Resident Table

Where Flag = 1;

Output:

Data Structure (can be modified as per your need)

Re: pull max(Date) from each month

MK_QSL — Wed, 30 Sep 2015 14:29:57 GMT

Temp:
Load 
  Date(Date#(Date,'D/M/YYYY')) as Date,
  MonthStart(Date#(Date,'D/M/YYYY')) as MonthYear
Inline
[
  Date
  21/9/2015
  5/9/2015
  12/9/2015
  1/10/2015
  5/10/2015
];


Left Join (Temp)
oad 
  Date(Max(Date)) as MaxDate, 
  MonthYear
Resident Temp
Group By MonthYear;


Final:
Load Date Resident Temp
Where Date = MaxDate;


Drop Table Temp;

Re: pull max(Date) from each month

jyothish8807 — Wed, 30 Sep 2015 14:45:17 GMT

Thanks a lot khus, it worked.

Regards

Re: pull max(Date) from each month

Anonymous — Wed, 30 Sep 2015 15:00:28 GMT

Hi Kush,

Technically you need max date while grouping on month.

try below sql :

SELECT MONTH, MAX(DATE)

FROM TABLE

GROUP BY MONTH

Above query will give month wise max date available in the table.

Ex :

Month	maxDate
1	1/31/2015 0:00
2	2/28/2015 0:00
3	3/31/2015 0:00

Thanks

Sattya

Re: pull max(Date) from each month

jyothish8807 — Wed, 03 Jan 2018 08:51:19 GMT

Hi Sunny,

Is there any way to achieve this without doing any join ?

Currently my load time has increased due to this logic, i am dealing with huge amount of data and i have implemented this logic in almost all tables.

Br,

Re: pull max(Date) from each month

jyothish8807 — Wed, 03 Jan 2018 08:51:27 GMT

Hi Kushal,

Is there any way to achieve this without doing any join ?

Currently my load time has increased due to this logic, i am dealing with huge amount of data and i have implemented this logic in almost all tables.

Br,

Re: pull max(Date) from each month

sunny_talwar — Wed, 03 Jan 2018 09:23:34 GMT

I think the problem might be the group by rather than the join itself (unless it is a many to many join)... may try to optimize your group by using the approach suggested here

Optimize Group By Performance

Re: pull max(Date) from each month

jyothish8807 — Wed, 03 Jan 2018 12:04:33 GMT

Hi Sunny,

My group by clause is a very basic one.

LOAD MonthYear,

date(max(Date),'DD-MM-YYYY') as Date

Resident Data

Group by MonthYear;

It should not create issues,I believe the join is creating the issue. I have almost 3 years data and it is very huge.

I am pulling the latest data of each month by using this logic and ignoring the old data in the dashboard.

Br,

Re: pull max(Date) from each month

sunny_talwar — Wed, 03 Jan 2018 12:08:40 GMT

Just for a kick of it... can you try this

Table:

LOAD Date,

Daily,

MonthName(Date) as MonthYear;

LOAD * Inline [

Date, Daily

21/9/2015, 1

5/9/2015, 2

12/9/2015, 3

1/10/2015, 4

5/10/2015, 5

];

Temp:

NoConcatenate

LOAD *

Resident Table

Order By MonthYear;

Join(Table)

LOAD MonthYear,

Max(Date) as Date,

1 as Flag

Resident Temp

Group By MonthYear;

DROP Table Temp;

ExtractedData:

LOAD Date as Date1

Resident Table

Where Flag = 1;

Re: pull max(Date) from each month

jyothish8807 — Wed, 03 Jan 2018 12:21:37 GMT

Sure sunny, I will give it a try It is an interesting analysis with the sort order

Br,