Issue with Incremental reload. Losing data that should not be lost

Mon, 16 Jul 2012 18:51:46 GMT

I am having an issue with incremental reload losing data. Below is a section of my script that is causing issues. I have narrowed this issue down to being caused to the WHERE NOT EXISTS part. i.e. I commented that out and the data I want shows up. I checked the first section of the SQL select and the data I want is not there, and the primarykeyJournaldtl is not there. I have no Idea what could be causing this as I have double checked to make sure it is not being loaded in the first section, and I do know that it is contained within the QVD.

This is beginning to give me a major headache so any help would be appreciated.

Let ThisExecTime= num(today());
Let ThisExecTimeyear= year(today());
Let ThisExecTimemonth= num(month(today()));
Let ThisExecTimeday= day(today());
Let LastExecTime =  ThisExecTime;                                                                
Let LastExecTimeyear=ThisExecTimeyear;
Let LastExecTimemonth=ThisExecTimemonth;
Let LastExecTimeday=ThisExecTimeday;
Journaldtl1:
SQL SELECT 
company  + bookid + cast(fiscalyear as varchar)+ cast(journalcode as varchar) + cast(journalnum as varchar) 
+ cast(journalline as varchar)  as primarykeyJournaldtl,
    *
    FROM gljrndtl where
 ((CAST((  STR( ( YEAR(posteddate) ) ) + '/' +  STR( MONTH(posteddate) ) + '/' +  STR( DAY(posteddate) )  ) AS DATETIME) ) >=
 CAST((  STR( ( $(LastExecTimeyear) ) ) + '/' +  STR( ($(LastExecTimemonth)) ) + '/' +  STR(($(LastExecTimeday)) )  ) AS DATETIME)  
 AND 
 (CAST((  STR( ( YEAR(posteddate) ) ) + '/' +  STR( MONTH(posteddate) ) + '/' +  STR( DAY(posteddate) )  ) AS DATETIME) ) <=
  CAST((  STR( ( $(ThisExecTimeyear) ) ) + '/' +  STR( ($(ThisExecTimemonth)) ) + '/' +  STR(($(ThisExecTimeday)) )  ) AS DATETIME) )   
;
Concatenate 
LOAD 
    *
    FROM [....qvd(qvd)] (qvd)
WHERE NOT EXISTS(primarykeyJournaldtl)
;
Inner Join SQL SELECT company,bookid,fiscalyear,journalcode,journalnum,journalline FROM gljrndtl;

Re: Issue with Incremental reload. Losing data that should not be lost

nagaiank — Mon, 16 Jul 2012 19:10:02 GMT

If you try to load both the data after commenting out

"WHERE NOT EXISTS(primarykeyJournaldtl)"

do you see any duplicate values in the field primarykeyJournaldtl ?

Re: Issue with Incremental reload. Losing data that should not be lost

Mon, 16 Jul 2012 19:23:10 GMT

I found the issue. It was along the lines of say

Journal Number=1362

Journal line=2

then I had another entry where

Journal Number=136

Journal line=22

giving me the same key. I will add '-' between the values to avoid this.

topic Issue with Incremental reload. Losing data that should not be lost in QlikView

Issue with Incremental reload. Losing data that should not be lost

Re: Issue with Incremental reload. Losing data that should not be lost

Re: Issue with Incremental reload. Losing data that should not be lost