Duplicate data in incremental load

[Anonymous]

Hi All,

I am facing an issue that i am getting duplicate  set of record for particular id

I am fetching the data from qvd fetching data on daily basis

ID	OrgLvl3Flag	OrgLvl4Flag	OrgLvl5Flag	IssueFlag	OrgLvl3	OrgLvl3Old	OrgLvl4	OrgLvl4old	OrgLvl5	OrgLvl5old
123123	Change	Change	Change	Modified	XXX		YYY		ZZZ
123123	No Change	No Change	No Change	Not Modified	XXX	XXX	YYY	YYY	ZZZ	ZZZ

$(vLatestFile_Snapshot):

LOAD distinct

      [Issue ID],

     [Organisation Level 3 (Issue)] ,

     [Organisation Level 4 (Issue)] ,

     [Organisation Level 5 (Issue)] ,

  // Floor (MakeDate(2017,08,08)) as

    System_Transaction_Date

FROM

$(vLatestFileName)(qvd);

left join($(vLatestFile_Snapshot))

LOAD distinct

     [Issue ID],

     [Organisation Level 3 (Issue)] as  [Organisation Level 3 (Issue) old],

     [Organisation Level 4 (Issue)] as  [Organisation Level 4 (Issue) old],

     [Organisation Level 5 (Issue)] as  [Organisation Level 5 (Issue) old]

FROM

$(vTransformQVD)\OrionDailySnapshot\$(vPrevFile_Snapshot).QVD(qvd);

$(vOrionTableName):

Load *,

if(Orglevel3Flag='Change' or Orglevel4Flag='Change' or Orglevel5Flag='Change','Modified','Not Modified')as RiskIssueFlag;

NoConcatenate

Load distinct  *,

if([Organisation Level 3 (Issue)]= [Organisation Level 3 (Issue) old],'No Change','Change') as Orglevel3Flag,

if([Organisation Level 4 (Issue)]= [Organisation Level 4 (Issue) old],'No Change','Change') as Orglevel4Flag,

if([Organisation Level 5 (Issue)]= [Organisation Level 5 (Issue) old],'No Change','Change') as Orglevel5Flag

Resident $(vLatestFile_Snapshot);

Drop Table $(vLatestFile_Snapshot);

Concatenate($(vOrionTableName))

LOAD [Issue ID],

     [Organisation Level 3 (Issue)],

     [Organisation Level 4 (Issue)],

     [Organisation Level 5 (Issue)],

     System_Transaction_Date,

     [Organisation Level 3 (Issue) old],

     [Organisation Level 4 (Issue) old],

     [Organisation Level 5 (Issue) old],

     Orglevel3Flag,

     Orglevel4Flag,

     Orglevel5Flag,

     RiskIssueFlag

FROM

D:\WorkArea\Shweta\Org Hier data\$(vOrionTableName).qvd

(qvd);

STORE  $(vOrionTableName) into D:\WorkArea\Shweta\Org Hier data\$(vOrionTableName).qvd(qvd);

[Anonymous]

No I think you  haven't understand the issue.

We loading the data from daily qvd,which comapre cureent date and prev date qvd data.Where first qvd of 20/08/2017include 123123 ID,but this is not exist in  19/08/2017 qvdin first load,main.qvd is not exist , entire code run before concatenation &the data stored in main qvd.

In second we are comparing  21/08/2017 and 20/08/2017 qvd as the ID 123123 ins already exist  in 20/08/2017  and also exist in 21/08/2017. it compare and generate the line of "No change". and it gets concatenate with main qvd which include the data 20/08/2017 "Change" record in Org Level3 ,it will generate 2 line for single ID

[PunamWagh]

Hope this will help you