topic Re: Create New Records in QlikView

Create New Records

Anonymous — Mon, 06 Jul 2015 18:17:48 GMT

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

Re: Create New Records

MK_QSL — Mon, 06 Jul 2015 20:00:29 GMT

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;

Re: Create New Records

Anonymous — Mon, 06 Jul 2015 21:00:39 GMT

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!

Re: Create New Records

Anonymous — Tue, 07 Jul 2015 02:59:26 GMT

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

Re: Create New Records

settu_periasamy — Tue, 07 Jul 2015 03:53:11 GMT

Hi,

Want like this?

find the attached.

Re: Create New Records

MK_QSL — Tue, 07 Jul 2015 05:37:57 GMT

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;

Re: Create New Records

Anonymous — Tue, 07 Jul 2015 20:14:20 GMT

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

Re: Create New Records

Anonymous — Tue, 07 Jul 2015 20:21:00 GMT

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!

Re: Create New Records

MK_QSL — Tue, 07 Jul 2015 20:30:15 GMT

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;

Re: Create New Records

Anonymous — Tue, 07 Jul 2015 21:16:40 GMT

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.

Re: Create New Records

MK_QSL — Wed, 08 Jul 2015 07:04:35 GMT

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;

Re: Create New Records

Anonymous — Wed, 08 Jul 2015 15:42:21 GMT

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

Re: Create New Records

MK_QSL — Wed, 08 Jul 2015 17:19:27 GMT

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.

Re: Create New Records

Anonymous — Wed, 08 Jul 2015 18:15:09 GMT

Hi Manish,

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

Frank

Re: Create New Records

MK_QSL — Wed, 08 Jul 2015 18:16:08 GMT

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