Maximum Date for Each Month From a Table in Scripting

[#1]

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.

[JonnyPoole · Accepted solution]

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;  

[MarcoWedel]

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

[MarcoWedel]

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