topic Re: Duplicate data in incremental load in QlikView

Duplicate data in incremental load

Anonymous — Mon, 21 Aug 2017 05:58:10 GMT

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);

Re: Duplicate data in incremental load

Anil_Babu_Samineni — Mon, 21 Aug 2017 06:18:23 GMT

May be check which table you are getting unique values by using Trace in Qlik

Re: Duplicate data in incremental load

arvind_patil — Mon, 21 Aug 2017 06:21:50 GMT

Hi Shweta,

Kindly provide sample app.

Thanks,

Arvind Patil

Re: Duplicate data in incremental load

mayuresh_d — Mon, 21 Aug 2017 09:11:09 GMT

Please check

"$(vPrevFile_Snapshot).QVD" this table. whether you r getting unique Issue Id or not. if that table has duplicate values then it will give you duplicate in final result as well.

Re: Duplicate data in incremental load

avinashelite — Mon, 21 Aug 2017 09:15:23 GMT

Which is your incremental key ?? If feel their is a issue with your incremental logic its self ..please check the logic and data once

Re: Duplicate data in incremental load

Anonymous — Mon, 21 Aug 2017 10:47:39 GMT

please find sample app

Re: Duplicate data in incremental load

Anonymous — Mon, 21 Aug 2017 11:57:36 GMT

PFA of Sample File

Re: Duplicate data in incremental load

Anonymous — Mon, 21 Aug 2017 11:58:30 GMT

PFA of Sample File

Re: Duplicate data in incremental load

Anonymous — Mon, 21 Aug 2017 11:59:26 GMT

PFA of Sample File as replied over other reply

Re: Duplicate data in incremental load

mayuresh_d — Mon, 21 Aug 2017 12:00:03 GMT

please check attached app. if its same as what u want.

Re: Duplicate data in incremental load

Anonymous — Tue, 22 Aug 2017 03:27:25 GMT

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

Re: Duplicate data in incremental load

PunamWagh — Tue, 22 Aug 2017 06:25:14 GMT

Hope this will help you