topic Generate new table based on date ranges in existing table? in QlikView

Generate new table based on date ranges in existing table?

Mon, 02 Apr 2012 14:15:41 GMT

Hi All

I have a table where each row with a start date an end date and a few other metrics. What I want to acheive in the load is to then split out these days into individual rows in a new table with a single date column and linking the same metrics. I'll try to illustrate below:

Exiting table:

Start_Date End_Date Name Address etc

01/04/2010 05/04/2010 Fred 1 Anystreet

01/02/2010 03/02/2010 Bob 2 AnotherStreet

Desired Result:

Date Name Address etc

01/04/2010 Fred 1 Anystreet

02/04/2010 Fred 1 Anystreet

03/04/2010 Fred 1 Anystreet

04/04/2010 Fred 1 Anystreet

05/04/2010 Fred 1 Anystreet

01/02/2010 Bob 2 AnotherStreet

02/02/2010 Bob 2 AnotherStreet

03/02/2010 Bob 2 AnotherStreet

Please can anyone advise how I could achieve this result?

Thanks

Dominic

Generate new table based on date ranges in existing table?

Clever_Anjos — Mon, 02 Apr 2012 14:19:31 GMT

Check you manual for IntervalMatch

Generate new table based on date ranges in existing table?

Mon, 02 Apr 2012 14:53:03 GMT

Thanks for the reply.

I've taken a look and it doesn't quite match my situation. I don't have 2 tables to match, I have a single table I want to split out into days for each line.

Appreciate the help.

Dominic

Generate new table based on date ranges in existing table?

Jason_Michaelides — Mon, 02 Apr 2012 14:57:09 GMT

You need to use a combination of Autogenerate(), While and Peek()

I'll put together a script and post shortly.

Generate new table based on date ranges in existing table?

Jason_Michaelides — Mon, 02 Apr 2012 14:57:55 GMT

Actually - you just need Peek()...

Generate new table based on date ranges in existing table?

Mon, 02 Apr 2012 15:01:41 GMT

Thanks Jason, look forward to seeing the script.

Generate new table based on date ranges in existing table?

Clever_Anjos — Mon, 02 Apr 2012 15:13:54 GMT

Use this example to generate a Calendar Table and then use it in IntervalMatch

/*******************************************************************
This example shows some common patterns for generating a
table of dates.
*******************************************************************/

/*
Set the ending date. Note the use of the num() function to make the loop work correctly.
The End Date can be set using other functions like, MakeDate(). Just make sure it's a number.
*/
LET vEndDate = num(today(1)); // End at today's date

/*
Set the start date. Subtract 1 to simplify the LOAD loop.
Again, make sure the value is a number by using num().
*/
LET vStartDate = num(MakeDate(2006,1,1)) -1; // Start at 2006-01-01.
/* Some other variations for setting the start date: */
// --12 months back from EndDate
//LET vStartDate = num( addMonths($(vEndDate),-12) );
// --Beginning of the current year
//LET vStartDate = num( yearStart(today(1)) ) -1;
// --Beginning of the prior year
//LET vStartDate = num( yearStart(addMonths($(vEndDate),-12)) ) -1;

/*
Note: Each example LOAD below generates a seperate table for
demonstration purposes. In a real application, there is typically only one date table.
*/

/*
Generate the date table using iterNo() to control the loop.
*/
DateMaster:
LOAD date($(vStartDate) + IterNo()) as Date
AUTOGENERATE 1
WHILE $(vStartDate) + IterNo() <= $(vEndDate)
;

/*
This example adds addtional dimensions to the table.
It's simplest to do this with a preceeding LOAD.
*/

DateMaster2:
LOAD
Date,
year(Date) as Year,
month(Date) as Month,
day(Date) as Day,
weekday(Date) as Weekday
;
LOAD date($(vStartDate) + IterNo()) as Date
AUTOGENERATE 1
WHILE $(vStartDate) + IterNo() <= $(vEndDate)
;

Re: Generate new table based on date ranges in existing table?

jonathandienst — Mon, 02 Apr 2012 15:36:56 GMT

Dominic

Given a start and end date for your analysis, loop over all the dates to bring in the rows by day:


For zi = zStartDate To zEndDate
           Let zDate = Date(zi);
          Result_Table:
          LOAD Date($(zi)) As Date,
                    Name,
                    Address1, ...
           Resident ExistingTable
          Where Start_Date <= Date($(zi)) And End_Date >= Date($(zi))
 Next

This script fragment assumes that zStartDate and zEndDate are numeric and that your source data is in ExistingTable. Adjust as required.

You could use fixed dates for start and end, or use the minimum and maximum dates from your source data.

Regards

Jonathan

Re: Generate new table based on date ranges in existing table?

Jason_Michaelides — Mon, 02 Apr 2012 15:51:19 GMT

Jonathan's solution is much better than my idea! Have attached a finished solution using his idea (seeing as I was doing one)...

Jason

Re: Generate new table based on date ranges in existing table?

Mon, 02 Apr 2012 16:32:41 GMT

Thanks for the replies. Finished work for today, will try implementing tomorrow and feedback. Dominic

Generate new table based on date ranges in existing table?

Tue, 03 Apr 2012 07:28:03 GMT

Really useful Jason, appreciate it, I swapped the code virtually directly and it worked great. I had to make one small amendment though for anyone else using Jason's example, you'll need to change the ordering of the resident loads to find mindate and maxdate (before the peek's), DESC should be ASC for mindate and ASC should be DESC for maxdate.

Generate new table based on date ranges in existing table?

Sun, 06 May 2012 16:15:55 GMT

Hello,

I've a table with Project Nos., StartdateProject,EnddateProject & Revenue

I need to a create a Calender so that on selecting a particular Year , all the projects starting or ending in this Year are displayed.

Moreover, on selecting the Quarters (Q1 or Q2 or Q3 or Q4) the Revenue should be shown only for that Quarter.

For eg. if a project has Yearly Revenue of 1000 & the project runs from 1 Jan, 2015 to 31 Dec, 2015

then on Selecting Q1, Revenue displayed should be 1000/4(=250).

Please help

Regards