Date generation

[rahulsud007]

ID	jan	rowno()	Year
AO	0	1	2015
AO	1	2	2015
AO	0	3	2015
AO	1	4	2015
AO	1	5	2015

Dear Experts, this is the format which I currently have

and I need the output by replacing Rowno with date. Such as

date

1-jan-2015

2-jan-2015

3-jan-2015

4-jan-2015

5-jan-2015. Can you please help me in this?

[#2]

Hi,

put it like,

rowno()&'-'&Month&'-'&Year as NewDate

[rahulsud007]

Hi harshal, thanks for your Feedback but the thing is that I am not getting the rowno() as 1,2,3,4,5 as I mentioned  it is as such 4021,

4022,4033.. so on

[#4]

Hi,

try something like that and if your jan is fix in field then you replace in makedate by 1

TEST:

LOAD * inline [

ID, jan ,rowno() ,Year

AO ,0 ,1, 2015

AO ,1, 2, 2015

AO, 0 ,3, 2015

AO ,1, 4, 2015

AO ,1, 5, 2015];

TEST2:

LOAD ID,

MakeDate(Year,jan,rowno()) as date

Resident TEST;

Regards

Vimlesh

[senpradip007]

Do you have any month field?

[#6]

Can you give sample of accessing data

or clarify that how you manage rows with days like 1 ot 31 rows.

Regards

Vimlesh

[senpradip007]

you can use like

makedate(year, month(rowno()), day(rowno())) as Date

[#8]

Hi,

May be it will match with yur req..

Test:

Load

ID,

Year,

date(Month,'MMM') as NewMonth

from source;

Load *,

rowno() as Key,

rowno() &'-'&NewMonth&'-'&Year as NewDate

resident Test;

[rahulsud007]

Hi guys, I do have multiple Month fields You can consider from Jan to December of last 10 years and need to generate a date field for it. But the things is all these month fields are in different table so I just gave a prototype of only one table. Also for february if it is a leap Year i should get date till 29, 28 n for month with 30 days ie april, june,september, november and rest for 31 days.

[#10]

Hi Rahul,

Try below script (You can change the values of vDateMin & vDateMax as per your requirement) -

LET vDateMin = Num(MakeDate(2015,1,1)); 

LET vDateMax = Floor(MonthEnd(Today())); 

TempCalendar: 

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber, 

Date($(vDateMin) + RowNo() - 1) AS TempDate 

AUTOGENERATE 1 

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Calendar: 

LOAD

rowno() AS REC,

Date(TempDate) AS CalendarDate, 

RESIDENT TempCalendar ORDER BY TempDate ASC; 

DROP TABLE TempCalendar; 

Regards,

Nitesh