Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Maximum Date for Each Month From a Table in Scripting

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_IdAct_DateAct_CountryTM_OpenTM_Flag
102-07-2014India23Open
229-07-2014Australia340Open
304-07-2014Hong Kong2643Open
405-07-2014Malasiya1711Open
506-07-2014Saudi Arabia2923Open
910-07-2014India1925Open
1011-07-2014Australia541Open
1101-08-2014Hong Kong764Open
1707-08-2014India1391Open
1808-08-2014Australia1390Open
1909-08-2014Hong Kong1549Open
2010-08-2014Malasiya437Open
2130-08-2014Saudi Arabia995Open

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.

Tags (2)
1 Solution

Accepted Solutions
Employee
Employee

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

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;  

3 Replies
Employee
Employee

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

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

Hi,

one solution:

QlikCommunity_Thread_131863_Pic1.JPG.jpg

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

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

Community Browser