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

Date conv works in Debug w/ limited load but not in full Reload???

I am loading 3 new datetime fields into a QV temp table.  Then, I am converting them from the date that QV translates it to.  This is working, no problem…. WHEN I DO A LIMITED LOAD IN DEBUG!!!  When I do a full RELOAD, it blanks out the new fields. I have even tried doing a 500,000 line load (which is more than the RELOAD amount would be) and it still works.  Have you seen anything like this?  We tried on 2 different machines thinking that it possibly was a memory issue, but we have ruled that out.  I have pasted the code and my results below in case someone can see something glaringly wrong.  Anyone else had this issue?  Ideas to try?  I've looked at the Windows Event Viewer, checked the log file, looked at my data to make sure there are no NULL values coming in, etc.  I am at loss.

raw_LNL_Agent_ContractCode_History:
Select WEEK as 'lach.EffectiveWeek1',  //This Field
AGT as 'lach.Agent_No1',
UM as 'lach.UMgr1',
BM as 'lach.BMgr1',
SA as 'lach.SA1',
RAD as 'lach.RAD1',
Partnership as 'lach.Partnership1',
DISTRCT as 'lach.District1',
AGY as 'lach.Agency1',
GRP as 'lach.Group1',
HIRE as 'lach.Hire_Date1',      
//This Field
ReportWeek as 'lach.ReportWeek1', 
// This Field
BranchOffice as 'lach.BranchOffice1'
from LNAGUMBM
;

LNL_Agent_ContractCode_History:
Load Date(floor(timestamp#(lach.EffectiveWeek1,'M/D/YYYY h:mm:ss[.fff] TT')),'YYYY-MM-DD') as lach.EffectiveWeek//This Field
lach.Agent_No1 as lach.Agent_No,

     lach.UMgr1 as lach.UMgr,
lach.BMgr1 as lach.BMgr,
lach.SA1 as lach.SA,
lach.RAD1 as lach.RAD,
lach.Partnership1 as lach.Partnership,
lach.District1 as lach.District,
lach.Agency1 as lach.Agency,
lach.Group1 as lach.Group,
Date(floor(timestamp#(lach.Hire_Date1,'M/D/YYYY h:mm:ss[.fff] TT')),'YYYY-MM-DD') as lach.HireDate, //This Field
Date(floor(timestamp#(lach.ReportWeek1,'M/D/YYYY h:mm:ss[.fff] TT')),'YYYY-MM-DD') as lach.ReportWeek //This Field
RESIDENT raw_LNL_Agent_ContractCode_History    

  

DateResults.jpg

1 Reply
chematos
Specialist II
Specialist II

Maybe when you do a full reload there are data that don't have a valid date format. I think Alt() function could help you, you can use this function to check if the fild has the format or formats you need, something like this:

Alt(Date(floor(timestamp#(lach.ReportWeek1,'M/D/YYYY h:mm:ss[.fff] TT')),'YYYY-MM-DD'), 'Invalid Date Format')

You should review the syntax in the help menu...

Hope this helps

Regards