Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Good day to you.
I have this requirement. Please guide.
I want to read one qvd file as follows.
QVD File having data (sample data) like this.
I want only records of starting date of every month and i dont want to current month details. In this eg , I want records of EMPID 10,40,60. Please guide me how to do it. Thanks in advance.
AAA:
LOAD * Inline [
EMPID, EMPN, CREDT
10,A,2/8/2014
20,B,7/8/2014
30,C,17/8/2014
40,D,4/9/2014
50,E,10/9/2014
60,F,3/10/2014
70,G,21/10/2014
80,T,6/11/2014
90,Y,27/11/2014
];
left join
LOAD Date(min(CREDT)) as CREDT, '1' as FlagMinDate Resident AAA
Group By Year(CREDT)*100+Month(CREDT);
Let me know ...
Load FirstSortedValue(EMPID,CREDT) Group by Year(CREDT) & Month(CREDT);
Load EMPID,EMPN,DATE#(CREDT,'DD/MM/YYYY') AS CREDT Inline [
EMPID,EMPN,CREDT
10,A,2/8/2014
20,B,7/8/2014
40,D,4/9/2014
50,E,10/9/2014
60,F,3/10/2014 ];
AAA:
LOAD * Inline [
EMPID, EMPN, CREDT
10,A,2/8/2014
20,B,7/8/2014
30,C,17/8/2014
40,D,4/9/2014
50,E,10/9/2014
60,F,3/10/2014
70,G,21/10/2014
80,T,6/11/2014
90,Y,27/11/2014
];
left join
LOAD Date(min(CREDT)) as CREDT, '1' as FlagMinDate Resident AAA
Group By Year(CREDT)*100+Month(CREDT);
Let me know ...
Thanks for the help. Its working.
T1:
Load *,
Month(CREDT) as Month;
LOAD
EMPID,
EMPN,
Date(Date#(CREDT,'D/M/YYYY')) as CREDT
Inline
[
EMPID, EMPN, CREDT
10, A, 2/8/2014
20, B, 7/8/2014
30, C, 17/8/2014
40, D, 4/9/2014
50, E, 10/9/2014
60, F, 3/10/2014
70, G, 21/10/2014
80, T, 6/11/2014
90, Y, 27/11/2014
];
Left Join
Load
Month,
Date(Min(CREDT)) as StartingDateofMonth
Resident T1
Group By Month;
NoConcatenate
Final:
Load
*
Resident T1
Where StartingDateofMonth = CREDT
and Month <> Month(Today()) ;
Drop Table T1;
Hi Varun,
Try this script
AAA:
LOAD *
MonthName(CREDT) AS Month
Inline [
EMPID, EMPN, CREDT
10,A,2/8/2014
20,B,7/8/2014
30,C,17/8/2014
40,D,4/9/2014
50,E,10/9/2014
60,F,3/10/2014
70,G,21/10/2014
80,T,6/11/2014
90,Y,27/11/2014
];
INNER JOIN
LOAD Month,
Date(min(CREDT)) as CREDT
Group By Month;
Hope this helps you.
Regards,
Jagan.
I know that this is a old thread, but I would be interested to know why the Group By statement is as it is. Wouldn't Group by Year(CREDT), Month(CREDT); give the same results?
Thanks and regards,
Seb