Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have been struggling to convert an oracle datetime filed which is a var char
for incremental load I need to compare between oracle and qlik.
For that reason I have converted the oracle varchar filed to time stamp
in oracle the date time filed is coming as : 201705300752435634343
after I convert to timestamp : 30-MAY-17 07.52.43.563434300 AM
now In qlik I think microsecond is only supported till 5th decimal place.
The formula I am using is :=(Timestamp(Timestamp#(DATETIME,'YYYYMMDDhhmmssfffffffff'),'DD-MMM-YY hh.mm.ss.fffffffff TT') )
which is giving the below result .The last 6 places are defaulted to zero and also month name is coming in small
can anyone please help me to convert the DATETIME field in qlik exactly like the oracle time stamp (i.e 30-MAY-17 07.52.43.563434300 AM) so that I can do a comparison in my incremental load.
For incremental loads I usually start from a minute before the previous Qlik load started, as in get from the database all rows amended after that time, and if the key of any of the incremental rows clashes with any existing Qlik rows overwrite the Qlik row with the incremental database row.
This eliminates any worries about date precision / conversion such as you describe.
[It also eliminates some other worries you may not have started worrying about yet]
Why don't you truncate the microseconds off completely - I doubt you need it to be that accurate. You could just do something like LEFT(TIMESTAMP, 14) on your Oracle date varchar field and then compare that with a similar timestamp format (eg 'DD-MMM-YY hh.mm.ss').
Bill,
Thanks for your reply.
your statutory warning really scared me ..
My incremental load runs every 5 min and we are not keen on reducing the performance in any sort if it is not at all mandatory.Can you please tell us a scenario where this offset of 1 minute can be effective?
Hi George,
Thanks for reply.
If I truncate the millisecond part and in my incremental load comparison I use DATETIME >=history QVD max time
do you think any scenario where I can miss data?
The "equal to" part should take care of the records which can be in the same time stamp after I truncate..if my understanding is correct.
A good question to which the answer is "it all depends...."
The scenario you mention is an incremental reload every 5 minutes, as opposed to say a daily one. I presume wish to get your Qlik data as near real time as possible.
The crux is the Qlik last reload datetime and the various Oracle last updated timestamps.
You ask re a potential scenario where things can go adrift and one is where Qlik time is out of sync with Oracle times. In the perfect world time is time and always the same but unfortunately the world is not perfect. Whilst it is good practise to have an time server sync'd to an external NTP server that all you internal servers sync their time to thus ensuring all you server are running on the same time I have seen set ups where such time syncing has failed or even simply not been set up. Qlik has used it server time and Oracle has used its time which has been adrift.
I have also seen a scenario was the Oracle timestamp field was populated from timestamps in incoming messaging with a time that was generated externally and messaging queues built up leading to a lag to when the database was actually updated with the timestamp as opposed to when the timestamp was generated externally.
Sometimes it can be best to populate the Qlik timestamp from the latest timestamp that Oracle returns. Some people trawl the Qlik QVD to find the latest timestamp loaded from Oracle but this trawl, if the QVD is large, can oft take a fair time in its own right.
As "it all depends...." could you outline your process flow, here I make up an example:
This is really basic scenario, not least because it won't take into account Oracle rows that have been deleted and will duplicate Oracle rows that have been amended.
I often find it useful to write the problem down first - have a look at this blog post by HIC, The Feynman Problem Solving Algorithm
I don't see any scenario where your >= logic would miss data so I would think you are good to go with that logic. Does your date stamp also pick up updated records?
Have a look at Rob Wunderlich's post on the fastest way to read a Max time from QVD for some good advice on how to get your comparison time from the QVD.
Good luck
George
I really do apologies for such a late reply... but Thanks a zillion for your Detailed reply Bill.It really removed some of my doubts. Thanks again
cheers
AD
Hi George,
You know what I posted this question a month ago and we didn't encounter any issues using that >=logic as of now..
Thanks for taking out time to reply!!