Create New Records

[Anonymous]

Hi All,

I'm working on an excel load file where i need some help with creating new records. I need these new records to do some calculation when it loads to memory. Please see below table. Column A, B,and C (up until 250) are from the inintial load file.  Column D is the new records that i wanted to create. Also, the last 2 items (1000; 9/30/2013) on column C & D is base on column A & B max().  Any input will be greatly appreciated! Thank you.

A               B               C          D

TempDate	Value	Final Value	Final_date
6/30/2011	100	-100	7/1/2011
9/30/2011	200	0	8/15/2011
12/31/2011	300	0	11/15/2011
3/31/2012	400	0	2/15/2012
6/30/2012	500	0	5/15/2012
9/30/2012	600	0	8/15/2012
12/31/2012	700	0	11/15/2012
3/31/2013	800	0	2/15/2013
6/30/2013	900	0	5/15/2013
9/30/2013	1000	250	8/15/2013
		1000	9/30/2013

This is the final piece that i wanted.

Final   Value	Final_date
-100	7/1/2011
0	8/15/2011
0	11/15/2011
0	2/15/2012
0	5/15/2012
0	8/15/2012
0	11/15/2012
0	2/15/2013
0	5/15/2013
250	8/15/2013
1000	9/30/2013

Frank

[MK_QSL]

Temp:

Load

  Company,

  Date(Date#(TempDate,'M/DD/YYYY')) as TempDate,

  Value,

  [Final Value]

Inline

[

  Company, TempDate, Value, Final Value

  ABC, 6/30/2011, 100, -100

  ABC, 9/30/2011, 200, 0

  ABC, 12/31/2011, 300, 0

  ABC, 3/31/2012, 400, 0

  ABC, 6/30/2012, 500, 0

  ABC, 9/30/2012, 600, 0

  ABC, 12/31/2012, 700, 0

  ABC, 3/31/2013, 800, 0

  ABC, 6/30/2013, 900, 0

  ABC, 9/30/2013, 1000, 250

  XYZ, 6/30/2011, 100, -100

  XYZ, 9/30/2011, 200, 0

  XYZ, 12/31/2011, 300, 0

  XYZ, 3/31/2012, 400, 0

  XYZ, 6/30/2012, 500, 0

  XYZ, 9/30/2012, 600, 0

  XYZ, 12/31/2012, 700, 0

  XYZ, 3/31/2013, 800, 0

  XYZ, 6/30/2013, 900, 0

  XYZ, 9/30/2013, 5000, 250

  Tom, 9/30/2012, 600, -200

  Tom, 12/31/2012, 700, 0

  Tom, 3/31/2013, 800, 0

  Tom, 6/30/2013, 900, 0

  Tom, 9/30/2013, 8000, 350

  Sam, 12/31/2012, 700, -155

  Sam, 3/31/2013, 9000, 125

];

Final:

Load

  Company,

  TempDate,

  [Final Value],

  If(Date(TempDate,'DDMM') = Date#('3006'), MonthStart(TempDate,1),

  MakeDate(Year(AddMonths(TempDate,-1)),Month(AddMonths(TempDate,-1)),15)) as Final_Date;

Load

  Company,

  TempDate,

  [Final Value],

  If(RowNo() = 1 or Company <> Previous(Company), MonthStart(TempDate,1),

  MakeDate(Year(AddMonths(TempDate,-1)),Month(AddMonths(TempDate,-1)),15)) as Final_Date

Resident Temp

Order By Company, TempDate;

Load

  Company,

  FirstSortedValue(TempDate,-TempDate) as Final_Date,

  FirstSortedValue(Value,-TempDate) as [Final Value]

Resident Temp

Group By Company;

Drop Table Temp;

[Anonymous]

Manish,

Thanks.Works great with my example as quarter date '6/30/2011'  are my oldest date where i want the Final Date to be '7/1/2011', which is correct. What if i have 2 companies with '3/31/2011' as the oldest date then the above "Date(TempDate,'DDMM') = Date#('3006')" will not works.  I was thinking maybe a variable/String that's define's "TempDate" as MinDate in the load script?

What i need is, 'only' the oldest quarter TempDate will be next month first date, else it will be prior month 15th day.  It's not by company, its by TempDate.

Let me know if you can help. Many Thanks

Frank

[MK_QSL]

Honestly speaking, I am just following your question and trying to get answer. If you tell me the exact logic behind your question, will help you better way.

[Anonymous]

Hi Manish,

Thanks for you help. I'm all set. I found what i needed.

Frank

[MK_QSL]

OK.... Kindly close the thread by selecting appropriate answer.. Thanks..