Autogenerate MonthEnd Date

[sunilkumarqv]

Need some help on autotgenerate below inputtable to outputtable

InputTable: 

DataName                       Date

A	21-01-2013
B	28-03-2013
C	28-06-2014
OutputTable:

DataName                       Date

A	21-01-2013
	31-01-2013
	28-02-2013
B	28-03-2013
	30-04-2013
	31-05-2013
C	28-06-2014

[JoaquinLazaro]

Hi Sunil:

Try MonthEnd(YourDate) function.

Hope this helps you

Joaquín

[MayilVahanan]

Hi

Are you looking like this

T1:

LOAD DataName,Date(Date) as Date INLINE [

    DataName,Date

    A, 21-01-2013

    B, 28-03-2013

    C, 28-06-2014

];

Concatenate

LOAD DataName, If(Month(Date)=1, MonthEnd(Date), MonthEnd(Date,-1)) as Date Resident T1;

DataName	Date
A	21-01-2013
A	31-01-2013
B	28-02-2013
B	28-03-2013
C	31-05-2014
C	28-06-2014

[sunilkumarqv]

That not give required output

I need

DataName	Date
A	21-01-2013
Null	31-01-2013
Null	28-02-2013
B	28-03-2013
null	31-05-2014
C	28-06-2014

[Ralf-Narfeldt]

I think this should do it:

T1:

LOAD *, Peek(Date,-1) As PrevDate;

LOAD DataName,Date(Date) as Date INLINE [

    DataName,Date

    A, 21-01-2013

    B, 28-03-2013

    C, 28-06-2014

];

Concatenate

LOAD Null() As DataName,

          MonthEnd(AddMonths(Date, - IterNo())) As Date

Resident T1 While IterNo() < Month(Date) - Month(PrevDate) + 1;

drop field PrevDate;

You do realize that the rows with Null values are not associated with the "previous" Dataname? I guess you have some date logic in your app that will fix that?

[sunilkumarqv]

Not working

[Ralf-Narfeldt]

What is not working?

It loads this data:

[Ralf-Narfeldt]

Now I see. There's a year-long jump between B and C which was not reflected in your original output table. You need to handle years as well.

I think you can get more help if you explain how you intend to use the data you load.

[#9]

Ralf is right, if you don't have specific pattern to be generated then go head with hard coded scripts. Something like this:

T1:

LOAD DataName,Date#(Date, 'DD-MM-YYYY') as Date

INLINE

[

DataName,Date

A,21-01-2013

B,28-03-2013

C,28-06-2014

];

Concatenate

LOAD NULL() AS DataName, Monthend(Date) as Date Resident T1

where Monthend(Date) < previous(Date) and DataName <> 'B'

order by Date desc;

Concatenate

LOAD NULL() AS DataName, Monthend(Addmonths(previous(Date), -1)) as Date Resident T1

where Monthend(Addmonths(Date, 1)) < previous(Date)

order by Date desc;

[MayilVahanan]

HI

Try like this

T1:

LOAD DataName,Date(Date) as Date INLINE [

    DataName,Date

    A, 21-01-2013

    B, 28-03-2013

    C, 28-06-2014

];

Concatenate

LOAD null() as DataName, If(Month(Date)=1, MonthEnd(Date), MonthEnd(Date,-1)) as Date Resident T1;

DataName	Date
A	21-01-2013
	31-01-2013
	28-02-2013
B	28-03-2013
	31-05-2014
C	28-06-2014