Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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);
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);
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);
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