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: 
parvez933
Creator
Creator

Incremental load without date field

Hi All,

I have data like below, and I do not have any date field.

ID, Name

10, A

20, B

30, C

Here the ask is to implement the incremental logic were the ID is the primary key.

I have gone through many posts and sites, but still winding and rounding.

So, is there anyone out there can make it clear that we can implement the incremental logic without date field or not?

Is it possible to create incremental logic on primary key!?

Regards,

M. Parvez

2 Solutions

Accepted Solutions
Vegar
MVP
MVP

You need to keep track of your ID somehow. You could try to do someting like this:

IF isnull(QvdCreateTime('IncrementalSource.qvd')) then

  IncrementalSource:
   LOAD * INLINE [ID, NAME];
   
   LET vMaxID = 0;

ELSE

  IncrementalSource:
   LOAD   
      ID,
      NAME 
   from 
      IncrementalSource.qvd (qvd);

  MAXID:
  LOAD Max(ID) as maxID RESIDENT Source;
  LET vMaxID = peek('maxID', -1,'MAXID');
END IF



CONCATENATE (IncrementalSource) LOAD   
   ID,
   NAME 
from 
   SOURCE
WHERE ID > $(vMaxID);

STORE Source into IncrementalSource.qvd (qvd);

 

View solution in original post

Vegar
MVP
MVP

Then you need to check if the ID exists. Below is how you could do it with QVD files as source, if other source then you need to adjust to fit its syntax.

IF isnull(QvdCreateTime('IncrementalSource.qvd')) then
   IncrementalSource:
   LOAD * INLINE [ID, NAME];
 
ELSE
   IncrementalSource:
   LOAD   
      ID,
      NAME 
   from 
      IncrementalSource.qvd (qvd);  
END IF

CONCATENATE (IncrementalSource) LOAD   
   ID,
   NAME 
from 
   SOURCE.QVD (QVD)
WHERE 
   NOT EXISTS(ID);

STORE Source into IncrementalSource.qvd (qvd);

View solution in original post

6 Replies
Vegar
MVP
MVP

You need to keep track of your ID somehow. You could try to do someting like this:

IF isnull(QvdCreateTime('IncrementalSource.qvd')) then

  IncrementalSource:
   LOAD * INLINE [ID, NAME];
   
   LET vMaxID = 0;

ELSE

  IncrementalSource:
   LOAD   
      ID,
      NAME 
   from 
      IncrementalSource.qvd (qvd);

  MAXID:
  LOAD Max(ID) as maxID RESIDENT Source;
  LET vMaxID = peek('maxID', -1,'MAXID');
END IF



CONCATENATE (IncrementalSource) LOAD   
   ID,
   NAME 
from 
   SOURCE
WHERE ID > $(vMaxID);

STORE Source into IncrementalSource.qvd (qvd);

 

parvez933
Creator
Creator
Author

Hi Vegar,

Thank You! for your reply!

This is the first solution, which I have given by looking into community.

But, the problem is the ID is not consistent ID can some time be 10 and
sometimes be 5 ( increase or decrease).

>From one of the post I got the knowledge that the ID / Primary key /
Sequence number should always be increasing but not vary.

Is it true!

Regards,

M. Parvez
Vegar
MVP
MVP

Then you need to check if the ID exists. Below is how you could do it with QVD files as source, if other source then you need to adjust to fit its syntax.

IF isnull(QvdCreateTime('IncrementalSource.qvd')) then
   IncrementalSource:
   LOAD * INLINE [ID, NAME];
 
ELSE
   IncrementalSource:
   LOAD   
      ID,
      NAME 
   from 
      IncrementalSource.qvd (qvd);  
END IF

CONCATENATE (IncrementalSource) LOAD   
   ID,
   NAME 
from 
   SOURCE.QVD (QVD)
WHERE 
   NOT EXISTS(ID);

STORE Source into IncrementalSource.qvd (qvd);
parvez933
Creator
Creator
Author

Hey Vegar,

I am thankful for your reply. I request one more help from you, please do bear with me.

Is it possible for you to share the sample code/ logic for the complete incremental [ INSERT, UPDATE, and DELETE ] for the shared example.

If possible, can you please share the logic for the both the cases with max(ID) and not exists(ID).

Please, as I am new it would be really helpful if I have the complete logic of incremental ( Insert, Update, and Delete )

Regards,

M. Parvez

parvez933
Creator
Creator
Author

Hi Vamsee, Thank You! the shared content is very useful. Regards, M. Parvez