How to create a Date field from Quarter and Month?

[dmohanty]

Hi All,

I have a table where two fields are there : Quarter (Q1, Q2, Q3, Q4) and Month (Jan 2013 - Dec 2013).

How can I create a Date and Day field at script level to contain all values?

Please suggest.

Regards!

[dmohanty]

Hi All,

Any help here please?

[tresesco]

If you mean all dates between Jan 2013 and Dec 2013, you should create a master calendar using autogenerate(). If your data is there at months level(most granular), you could think of creating one date for each month, using monthstart().

[dmohanty]

Hi Tresesco,

Could you please show an example for both the cases you have shared?

[tresesco]

I am too lazy to creat a sample. Please share your sample app with sample data and share. That would help me understand your data and suggest a better solution.

[Anonymous]

Hi,

     As u said, you need all the dates from jan to dec for the year 2013. Create a calendar with Jan 1st as the start date and Dec 31st as the end date. Use Autogenerate(). Hope it helps.

[dmohanty]

Hi Tresesco,

Attached is the sample sheet: Contains 2 tabs:

1st Tab Revenue: Column B and Column C (highlighted)

2nd tab OB: Column AP (highlighted)

My motto is to create and take the Date fields from above two tables and store them in a Link Table to be used as a Master Date Table.

Hope I am clear. Please help.

[#8]

Try this

Temp:

Load * Inline [
No, Price, Date
1, 2500, Jan 2013
2, 100, Feb 2013
3, 680, Mar 2013
4, 1500, Apr 2013
5, 920, May 2013
6, 250, Jun 2013
7, 1000, Jul 2013
8, 6800, Aug 2013
9, 150, Sep 2013
10, 92, Oct 2013
11, 450, Nov 2013
12, 750, Dec 2013
];

ResTemp:
NoConcatenate
Load
No,
Price,
Date(Makedate(SubField(Date,' ',2),SubField(Date(Date#(Date,'MMM YYYY'),'MM YYYY'),' ',1)),'DD/MM/YYYY') as Date
Resident Temp;
Drop Table Temp;

[dmohanty]

Hi Tresesco,

My aim is to create a model like this attached. From the tables I have shared above.

[mangalsk]

Hello,

PFA

It  creates date from 1/1/213 till today

Regards