Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
There is a table consisting of two fields, which gets updated two times a day.
I need to get incremental logic implemented for the second time reload.
See as per attached sample:
A run will always be a full load.
And in B run incremental will be applied.
So For B run I just want the records which are either Inserted , Updated or deleted.
Primary key can be [vehicle Code] &'-'&[Sales Code],
As per sample my output after B Run Should be :
Vehicle Code | Sales Code |
B | S1 |
D | S12 |
D | S11 |
E | S1 |
E | S3 |
If I get time then I may create one but as of now I think you may use a field to identify the Reload time.
since it will run two times a day so must be aware of the exact time the job runs. so if the reload time is already in today then it will be the second time and you can run the script for incremental load else it should be a full load from source table.
Do you want to keep B S1 or B S2?
B S2 was deleted...
HI Robert ,
I require
B S1
Because with these values only I will be able to do my further transformations.
Regards
Nitin
Req:
So For B run I just want the records which are either Inserted , Updated or deleted.
B S2 has been deleted
B S1 remains
but you are saying that you need to keep B S1.
Are they only 2 entry per Vehicle?
Why not keep A S1 then.?
Hi Robert,
I want the records which are Inserted or Updated,
And if the record from A run is deleted then all other combinations of Vehicle code and Sales Code should also be available for that specific Vehicle Code.
No there may be several Sales Code for a particular Vehicle.
So as B S2 is deleted , so S1 for B is also required.
and there was no change for A , so That entry is not required in b run
May be try this..
Directory;
//One Time Reload
//A:
//LOAD [Vehicle Code]&'-'&[Sales Code] as Key,
// [Vehicle Code],
// [Sales Code]
//FROM
//[TEST (1).xlsx]
//(ooxml, embedded labels, table is [A RUN]);
//
//STORE A into A.qvd(qvd);
//
//DROP Table A;
A:
LOAD Key as temp_key,
[Vehicle Code],
[Sales Code]
FROM
A.qvd
(qvd);
NoConcatenate
B:
LOAD [Vehicle Code]&'-'&[Sales Code] as Key,
[Vehicle Code],
[Sales Code]
FROM
[TEST (1).xlsx]
(ooxml, embedded labels, table is [B RUN]) Where not Exists(temp_key,[Vehicle Code]&'-'&[Sales Code]);
Concatenate(B)
LOAD temp_key as Key,
[Vehicle Code],
[Sales Code]
Resident A Where not Exists(Key,temp_key);
Inner Join(B)
LOAD [Vehicle Code]&'-'&[Sales Code] as Key,
[Vehicle Code],
[Sales Code]
FROM
[TEST (1).xlsx]
(ooxml, embedded labels, table is [B RUN]);
DROP Table A;
EXIT Script;
Sample attached