Query to read min date of every month.

[varunvarma]

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.

[anbu1984]

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 ];

[alexandros17 · Accepted solution]

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 ...

[varunvarma]

Thanks for the help. Its working.

[MK_QSL]

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;

[jagan]

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.

[#7]

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