Pull Dates, Doing Comparisons and creating a master calendar

[#1]

Hey Everyone-

I've been stumped on this.  I have a situation where I receive raw data (sometimes via csv, other times via excel, etc.) where the dashboard needs to compare data based on "pull date."

In other words, say user A does a report and pulls the data in January '11.  He/she produces his/her dashboard/report in excel.

Next month (Obviously Feb '11), User A pulls the data again.  Same data set (somethings have obviously changed).  Now not only does he/she need to produce his/her dashboard/report, but also needs to compare Data set from Feb to Dataset in January.

So on and so forth.

As time goes on, these pulls will need to be looked at by quarter and by year, as well as comparing year over year as well as quarter over quarter. Which in my mind, we need to create some sort of master calendar from this data set.

My question is, the dataset that we receive has no "pull date."  How do we apply the pull date into the table?  Ideas on creating a master calendar?

Here's how the structure looks like:

Pull #1: April 2011

Client Name, Division, Brand, Product, Campaign Name, Vendor Name, Campaign Start Date, Campaign End Date, Spend $

Coke, North America, Classic, Coke, 2011 Initiatives, Google, 1/1/11, 3/31/11, $10,000,000.00

Jenny Craig, Foods, Diet Food, Chocolate Bar, 2011 1H Diet Foods, Yahoo, 1/1/11, 6/30/11, $1,000,0000

Pull #2: May 2011

Client Name, Division, Brand, Product, Campaign Name, Vendor Name, Campaign Start Date, Campaign End Date, Spend $

Coke, North America, Classic, Coke, 2011 Initiatives, Google, 1/1/11, 3/31/11, $9,000,000.00

Jenny Craig, Foods, Diet Food, Chocolate Bar, 2011 1H Diet Foods, Yahoo, 1/1/11, 6/30/11, $2,000,0000

Please note that Campaign Start and Campaign End Dates *do not* factor in comparisons. Only Pulls are compared, but pull date is *not* something that appears in the tables.

Thanks!

[johnw]

You can add fields to tables easily:

LOAD
a bunch of fields
,today() as PullDate
FROM whatever source you're loading
;

What sort of master calendar do you want?  Here's a very basic one:

PullCalendar:
LOAD *
,date(yearstart(PullDate),'YYYY') as PullYear
,date(monthstart(PullDate),'MMM YYYY') as PullMonth
;
LOAD date(fieldvalue('PullDate',recno())) as PullDate
AUTOGENERATE fieldvaluecount('PullDate')
;

[#3]

John-

As usual, you are an amazing resource.  I cannot thank you enough.  One question I had is, because we are using today().  Wouldn't there be an issue if you had to reload the data?  Wouldn't the PullDate be reset?

[johnw]

I assumed that if you loaded the file again, that was a new pull of the data, so should have a new date.  If not, then what date do you want?  If the data is always loaded from a file, is it the date the file was most recently modified?  I suppose that makes more sense, though I still don't know if it's what you want.  You could probably do this if so:

,date(floor(filetime('Your file name here'))) as PullDate

You might want to use a PullTimestamp instead of just a PullDate.

[#5]

It's more that each new file will have a new file extension. Something like this:

CompetitiveSpend_060111.xls  (Load/Pull date: 6/1/11)

CompetitiveSpend_071211.xls  (Load/Pull date: 7/12/11)

In my load script, I'm using a wildcard recognzie this nomenclature (these data files are small enough that incremental loads would be overkill).

[johnw · Accepted solution]

Perhaps this?

,date(date#(textbetween(filename(),'_','.xls'),'MMDDYY')) as PullDate

This should work even when using wildcards to pull in multiple files, though I haven't tried it.

[#7]

John, as usual, you are the man.