topic Time based Load from Oracle DB Query? in QlikView

Time based Load from Oracle DB Query?

Thu, 10 Feb 2011 16:05:56 GMT

Hi All,

I am new to QV and was really looking forward to know how we could control the data with the date restriction while creating QVDs from Oracle DB? I have more than 5 years of data sitting in the DB, but my client wants only the current year - 2 years data (2009, 2010, Current Year) how to write a load function? and moreover what do you guys think is the best practise for creating QVDs and maintaining for this kind of project? I could do a hard code and define select * from Table WHERE Date >= 2009, however if I do this, what happens when the year changes?? Please suggest me

Thanks for your help guys.

ANDY

Time based Load from Oracle DB Query?

Thu, 10 Feb 2011 16:40:00 GMT

Hello Experts,

Any takers please? I can see there are a lot of experienced people around here...and was just thinking if anyone of you is working with Oracle DBs and Loading data with Time restrictions?

ANDY

Time based Load from Oracle DB Query?

Fri, 11 Feb 2011 10:22:28 GMT

I used to load QVDs with time restriction but don't any more. I have however this code that might be usefull for you in your QVDs: where ... and "COMMENT_DATE">=to_date('2009-01-01','yyyy-mm-dd'); (replace "COMMENT_DATE" with your date name)

If your customer always only want the current year + the previous two (so in 2012 they want 2012, 2011, 2010) then you need to do some sort of delete 2009 from you QVDs and that I have never done.

Maybe they could live with having 2009 -> in the QVDs and then in your QVW you could stipulate Load ... resident QVD where "COMMENT_DATE">= today(0)-some days. The problem here is that some days in order for you not to limit to much has to be 3 years. You could maybe do some sort of where num(Year("COMMENT_DATE"))>=today-3.

Time based Load from Oracle DB Query?

Fri, 11 Feb 2011 12:34:01 GMT

Hello,

Thanks for the reply, Even I was thinking of the same solution, in the sense, I will have data since 2009 in QVDs, however I should pickup the data only from 2010 in QVW for the coming year and then write something like CurrentYear()-2 so that it shouldnt be done manually everytime.... But was thinking if we can really do this....and if we did how it will be....

Time based Load from Oracle DB Query?

Fri, 11 Feb 2011 14:02:07 GMT

I tested this and is works for the QVW

where (num(year(Date))>=num(year(today(0)))-2);