Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Hi Sunil:
Try MonthEnd(YourDate) function.
Hope this helps you
Joaquín
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 |
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 |
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?
Not working
What is not working?
It loads this data:
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.
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;
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 |