Loading multiple years of data and getting duplicates

heathqm — Mon, 23 Nov 2020 14:09:10 GMT

Hello,

I am trying to load two different xml files with the same fields. When I load one it works fine, but when I load the second one it is giving me duplicates. One file is 2021 detail and the other file is 2020 detail. For instance there are two 2018 years but I want to combine them.

set v_FY_counter = 1;
do while v_FY_counter < 8
Load

[BudgetYear]&'--'&[BudgetCycle]&'--'&[ServiceAgencyName]&'--'&[SubmissionDate]&'--'&[ProgramElementNumber] AS [Program Element ID],
[BudgetYear] + ($(v_FY_counter) - 3) as [PEC Funding Year],
if($(v_FY_counter) = 1,[ProgramElementFunding/PriorYear],
if($(v_FY_counter) = 2,[ProgramElementFunding/CurrentYear],
if($(v_FY_counter) = 3,[ProgramElementFunding/BudgetYearOneOCO],
if($(v_FY_counter) = 4,[ProgramElementFunding/BudgetYearTwo],
if($(v_FY_counter) = 5,[ProgramElementFunding/BudgetYearThree],
if($(v_FY_counter) = 6,[ProgramElementFunding/BudgetYearFour],
if($(v_FY_counter) = 7,[ProgramElementFunding/BudgetYearFive],

null()))))))) as [PEC Funding Amount]

FROM [lib://2021.xml]
(XmlSimple, table is [MasterJustificationBook]);
Let v_FY_counter = v_FY_counter + 1;
// exit script;
Loop

set v_FY_counter = 1;
do while v_FY_counter < 8
Load

[BudgetYear]&'--'&[BudgetCycle]&'--'&[ServiceAgencyName]&'--'&[SubmissionDate]&'--'&[ProgramElementNumber] AS [Program Element ID],
[BudgetYear] + ($(v_FY_counter) - 3) as [PEC Funding Year],

if($(v_FY_counter) = 1,[ProgramElementFunding/PriorYear],
if($(v_FY_counter) = 2,[ProgramElementFunding/CurrentYear],
if($(v_FY_counter) = 3,[ProgramElementFunding/BudgetYearOneOCO],
if($(v_FY_counter) = 4,[ProgramElementFunding/BudgetYearTwo],
if($(v_FY_counter) = 5,[ProgramElementFunding/BudgetYearThree],
if($(v_FY_counter) = 6,[ProgramElementFunding/BudgetYearFour],
if($(v_FY_counter) = 7,[ProgramElementFunding/BudgetYearFive],

null()))))))) as [PEC Funding Amount]

FROM [lib://020.xml]
(XmlSimple, table is [MasterJustificationBook/]);
Let v_FY_counter = v_FY_counter + 1;
// exit script;
Loop

Re: Loading multiple years of data and getting duplicates

edwin — Mon, 23 Nov 2020 19:00:15 GMT

can you provide sample data and point out what you perceive to be duplicates?

topic Loading multiple years of data and getting duplicates in QlikView

Loading multiple years of data and getting duplicates

Re: Loading multiple years of data and getting duplicates