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
  Date(Date#(TempDate,'M/DD/YYYY')) as TempDate,
  Value,
  [Final Value]
Inline
[
  TempDate, Value, Final Value
  6/30/2011, 100, -100
  9/30/2011, 200, 0
  12/31/2011, 300, 0
  3/31/2012, 400, 0
  6/30/2012, 500, 0
  9/30/2012, 600, 0
  12/31/2012, 700, 0
  3/31/2013, 800, 0
  6/30/2013, 900, 0
  9/30/2013, 1000, 250
];


Final:
Load
  [Final Value],
  If(RowNo() = 1, MonthStart(TempDate,1), MakeDate(Year(AddMonths(TempDate,-1)),Month(AddMonths(TempDate,-1)),15)) as Final_Date
Resident Temp
Order By TempDate;
Load 
  FirstSortedValue(TempDate,-TempDate) as Final_Date,
  FirstSortedValue(Value,-TempDate) as [Final Value] 
Resident Temp;


Drop Table Temp;

[Anonymous]

Manish,

Works like a charm so far. Will let you know if it works with my xlsx file where i have multiple companies and TempDate. In the mean time, im trying to understand how this script works.

If(RowNo() = 1, MonthStart(TempDate,1), MakeDate(Year(AddMonths(TempDate,-1)),Month(AddMonths(TempDate,-1)),15)) as Final_Date 

Thank You very much!

[Anonymous]

Hi Manish,

I've tried with mulitple Companies. It didnt works, only works with one company such as below.  I should mention on my inital post where it will have multiple companies.  Please let me know if you can help with below items.

Works only with one company:

Company	TempDate	Value	Final_Date	[Final Value]
				-
ABC Corp	6/30/2011	100	7/1/2011	-100
ABC Corp	9/30/2011	200	8/15/2011	0
ABC Corp	12/31/2011	300	11/15/2011	0
ABC Corp	3/31/2012	400	2/15/2012	0
ABC Corp	6/30/2012	500	5/15/2012	0
ABC Corp	9/30/2012	600	8/15/2012	0
ABC Corp	12/31/2012	700	11/15/2012	0
ABC Corp	3/31/2013	800	2/15/2013	0
ABC Corp	6/30/2013	900	5/15/2013	0
ABC Corp	9/30/2013	1000	8/15/2013	250
-	-	-	9/30/2013	1,000
-	-	-	-	-

Need Help with bleow:

Item #1 - is it possilble to include the company name on the very last row? such as in this example. without a name for this last row, i cant use listbox to filter.

Company	TempDate	Value	Final_Date	[Final Value]
ABC Corp	-	-	9/30/2013	1,000

Item#2 -  is it possible to show all first date for all companies as 7/1/2011 for TempDate 6/30/2011?

Please see attached QVW and expected Final Output in xls format. Many Thanks if you can help.

Frank

[settu_periasamy]

Hi,

Want like this?

find the attached.

[MK_QSL · Accepted solution]

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

];

Final:

Load

  Company,

  [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]

Hi Manish,

I'm trying to tweet this IF statement a bit and couldn't figure it out. Hope you can help again. I want to replace it with "min(TempDate)" for  "RowNo() = 1" but it error out. What i want is:

If TempDate = the min date (6/30/xxxx) then Final Date will be next month first day (7/1/xxxx)

Else Final Date will be 15th day of previous month.

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

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

example:

Company	TempDate	Final_Date	[Final Value]	Final_Date (Good)
Tom	12/31/2012	1/1/2013	-150	11/15/2012
Tom	3/31/2013	2/15/2013	450	2/15/2013
Tom	-	3/31/2013	8,800	3/31/2013

Attached Excel file for reference.

Many Thanks!

Frank

[Anonymous]

Close to what i'm looking for. I'm still leaning towards Manish solution. I think once i figure out the lf Statement that i posted then i should be good. Thanks for your help Settu!

[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

];

Final:

Load

  Company,

  [Final Value],

  If(TempDate = MonthEnd(TempDate), 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]

Hi. Thanks for the reply.

Hope i didnt confuse you. The above IF statement doesnt works. 6/30/2011 is the "min" TempDate where company ABC and XYZ has for the month start date.  Final_Date for this two company should be 7/1/2011.

What i expect for the If Statement is:

"If TempDate = the min date (6/30/2011) then Final Date will be next month first day (7/1/2011)

Else Final Date will be 15th day of previous month. (5/15/2011)"

I have two other companies which are correct:

Sam - Tempdate=12/31/2012; Final_Date=11/15/2012

Tom - Tempdate=9/30/2012; Final_Date=8/15/2012

Hope you can help out again! Many Thanks!.

Attached QVW.