Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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;

2 Replies
nagaiank
Valued Contributor III

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

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 ?

Not applicable

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

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.