Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pull Dates, Doing Comparisons and creating a master calendar

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!

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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.

View solution in original post

6 Replies
johnw
Champion III
Champion III

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')
;

Not applicable
Author

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
Champion III
Champion III

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.

Not applicable
Author

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
Champion III
Champion III

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.

Not applicable
Author

John, as usual, you are the man.