Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
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
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