topic Re: Re: Maximum Date for Each Month From a Table in Scripting in QlikView

Maximum Date for Each Month From a Table in Scripting

Wed, 03 Sep 2014 20:58:37 GMT

Hi All,

I have a situation where I have thousand row of data to the corresponding dates on the calendar. Now I want to pull the data only for the maximum date of each month in load script. Please help or suggest a solution for this.

For example

Act_Id	Act_Date	Act_Country	TM_Open	TM_Flag
1	02-07-2014	India	23	Open
2	29-07-2014	Australia	340	Open
3	04-07-2014	Hong Kong	2643	Open
4	05-07-2014	Malasiya	1711	Open
5	06-07-2014	Saudi Arabia	2923	Open
9	10-07-2014	India	1925	Open
10	11-07-2014	Australia	541	Open
11	01-08-2014	Hong Kong	764	Open
17	07-08-2014	India	1391	Open
18	08-08-2014	Australia	1390	Open
19	09-08-2014	Hong Kong	1549	Open
20	10-08-2014	Malasiya	437	Open
21	30-08-2014	Saudi Arabia	995	Open

So the solution from the script should get me row num 2 as 340 for July-2014 and row num 21 for Aug 2014. Hope this make the query clear to answer.

Many thanks in advance.

Re: Maximum Date for Each Month From a Table in Scripting

JonnyPoole — Wed, 03 Sep 2014 21:32:03 GMT

Here is a sample script based on a source table above:

temp:

LOAD

Act_Id,

Date(date#(Act_Date,'DD-MM-YYYY')) as Act_Date,

Day(Date(date#(Act_Date,'DD-MM-YYYY'))) as Day,

Month(Date(date#(Act_Date,'DD-MM-YYYY'))) as Month,

Year(Date(date#(Act_Date,'DD-MM-YYYY'))) as Year,

Act_Country,

TM_Open,

TM_Flag

FROM

(ooxml, embedded labels, table is Sheet1);

Data:

load

Month,

Year,

max(Act_Date) as Act_Date

Resident temp

group by Month,Year;

left join (Data)

load

Act_Id,

Act_Date,

Day,

Act_Country,

TM_Open,

TM_Flag

resident temp;

drop table temp;

Re: Maximum Date for Each Month From a Table in Scripting

MarcoWedel — Wed, 03 Sep 2014 22:10:45 GMT

Hi,

one solution:

LOAD FirstSortedValue(Act_Id, -Act_Date) as Act_Id,
    FirstSortedValue(Act_Date, -Act_Date) as Act_Date,
    FirstSortedValue(Act_Country, -Act_Date) as Act_Country,
    FirstSortedValue(TM_Open, -Act_Date) as TM_Open,
    FirstSortedValue(TM_Flag, -Act_Date) as TM_Flag
FROM [http://community.qlik.com/thread/131863] (html, codepage is 1252, embedded labels, table is @1)
Group By MonthName(Act_Date);

hope this helps

regards

Marco

Re: Re: Maximum Date for Each Month From a Table in Scripting

MarcoWedel — Wed, 03 Sep 2014 22:15:59 GMT

or maybe:

LOAD * FROM [http://community.qlik.com/thread/131863] (html, codepage is 1252, embedded labels, table is @1);
Right Join
LOAD Max(Act_Date) as Act_Date
Resident table1
Group By MonthName(Act_Date);

hope this helps

regards

Marco