Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Want to see what's currently in public preview? We've pulled it all together in one place. Check it out here
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable
‎2015-07-06 02:17 PM

Create New Records

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

TempDateValueFinal ValueFinal_date
6/30/2011100-1007/1/2011
9/30/201120008/15/2011
12/31/2011300011/15/2011
3/31/201240002/15/2012
6/30/201250005/15/2012
9/30/201260008/15/2012
12/31/2012700011/15/2012
3/31/201380002/15/2013
6/30/201390005/15/2013
9/30/201310002508/15/2013
10009/30/2013

This is the final piece that i wanted.

Final
  Value		Final_date
-1007/1/2011
08/15/2011
011/15/2011
02/15/2012
05/15/2012
08/15/2012
011/15/2012
02/15/2013
05/15/2013
2508/15/2013
10009/30/2013

Frank

Labels (1)
Labels
3,112 Views
0 Likes
1 Solution

Accepted Solutions
MK_QSL
MVP
MVP
‎2015-07-07 01:37 AM

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;

View solution in original post

2,308 Views
0 Likes
14 Replies
MK_QSL
MVP
MVP
‎2015-07-06 04:00 PM 

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;
2,308 Views
0 Likes
Anonymous
Not applicable
‎2015-07-06 05:00 PM
Author

In response to MK_QSL

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!

2,308 Views
0 Likes
Anonymous
Not applicable
‎2015-07-06 10:59 PM
Author

In response to 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:

CompanyTempDateValueFinal_Date[Final Value]
-
ABC Corp6/30/20111007/1/2011-100
ABC Corp9/30/20112008/15/20110
ABC Corp12/31/201130011/15/20110
ABC Corp3/31/20124002/15/20120
ABC Corp6/30/20125005/15/20120
ABC Corp9/30/20126008/15/20120
ABC Corp12/31/201270011/15/20120
ABC Corp3/31/20138002/15/20130
ABC Corp6/30/20139005/15/20130
ABC Corp9/30/201310008/15/2013250
---9/30/20131,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.

CompanyTempDateValueFinal_Date[Final Value]
ABC Corp--9/30/20131,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

Final Output.xls
Test.qvw
2,308 Views
0 Likes
settu_periasamy
Master III
Master III
‎2015-07-06 11:53 PM

In response to Anonymous

Hi,

Want like this?

pivot.JPG

find the attached.

Test (4).qvw
2,308 Views
0 Likes
MK_QSL
MVP
MVP
‎2015-07-07 01:37 AM

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;

2,309 Views
0 Likes
Anonymous
Not applicable
‎2015-07-07 04:14 PM
Author

In response to MK_QSL

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:

CompanyTempDateFinal_Date[Final Value]Final_Date (Good)
Tom12/31/20121/1/2013-15011/15/2012
Tom3/31/20132/15/20134502/15/2013
Tom-3/31/20138,8003/31/2013

Attached Excel file for reference.

Many Thanks!

Frank

Final Output_1.xls
2,308 Views
0 Likes
Anonymous
Not applicable
‎2015-07-07 04:21 PM
Author

In response to settu_periasamy

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!

2,308 Views
MK_QSL
MVP
MVP
‎2015-07-07 04:30 PM

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;

2,308 Views
0 Likes
Anonymous
Not applicable
‎2015-07-07 05:16 PM
Author

In response to MK_QSL

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.

Test_2.qvw
2,308 Views
0 Likes