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
