Do While loop logic

I am trying to load data from multiple qvd:s and then save it like

LET Start = AddMonths(makedate(year(Today()), 1, 1),0);
LET End = AddMonths(makedate(year(Today()), 12, 31),0);
 
do while Start <= End
let YearMM = Year(date('$(Start)','YYYY-MM-DD'))*100+Month(date('$(Start)','YYYY-MM-DD'));  
NoConcatenate
 
TMP:
LOAD
    Field1,
    Field2,
    Field3
  
from [path/File_$(YearMM).qvd]
(qvd);
 
 
left Join(TMP)
Load
  Field1,
    Field2
    
FROM [Path/file2.qvd]
(qvd);
 
 
 
left join(TMP)
load distinct
Field1
    max(YearMM) as YearMM, 
    1 as MaxYearMM
resident TMP
group by Field1;

 
Drop Table TMP;
LET Start = date(AddMonths('$(Start)',1),'YYYY-MM-DD');     
LOOP

NoConcatenate
EndTable:
 
load 
*
resident TMP
where MaxYearMM=1;
 

store EndTable into [Path/endfile.qvd] (qvd);

But i can not get the loop logic to work so at the end it would go correctly. Currently it gives me 12 TMP tables and loads only from the first. Any tips how to fix?

It looks like you're joining those tables together in your loop into the [TMP] table but then just immediately dropping it before the end of the loop?

I think what you need is something like this:

Let Start = AddMonths(MakeDate(Year(Today()), 1, 1), 0);
Let End = AddMonths(MakeDate(Year(Today()), 12, 31), 0);

// CHANGE #1
[EndTable Load]: Load * Inline [Field1];

Do While Start <= End
    
    Let YearMM = Year(Date('$(Start)', 'YYYY-MM-DD')) * 100 + Month(Date('$(Start)', 'YYYY-MM-DD'));
    
    [TMP]:
    NoConcatenate Load
        [Field1]
      , [Field2]
      , [Field3]
    From [$(vStorePath)/File_$(YearMM).qvd] (QVD);

    Left Join([TMP])
    Load
        [Field1]
      , [Field2]
    From [$(vStorePath)/file2.qvd] (QVD);

    Left Join([TMP])
    Load Distinct
        [Field1]
      , Max([YearMM]) as [YearMM]
      , 1 as [MaxYearMM]
    Resident [TMP]
      Group By [Field1]
    ;
    
    // CHANGE #2
    Concatenate ([EndTable Load]) Load * Resident [TMP];
    
    Drop Table [TMP];
    
    Let Start = Date(AddMonths('$(Start)', 1), 'YYYY-MM-DD');
    
Loop

[EndTable]:
NoConcatenate Load *
Resident [EndTable Load] // <--------- // CHANGE #3
  Where [MaxYearMM] = 1
;

// CHANGE #4
Drop Table [EndTable Load];

Store [EndTable] Into [$(vStorePath)/endfile.qvd] (QVD);

Besides some formatting, here are the changes I made in the script:

Change #1

I added a new empty table called [EndTable Load] before the loop starts. This is because we'll need a table that can accumulate the data from each loop through those files. It seems like you were trying to achieve that with just the [TMP] table but then you can get stuck trying to figure out when to use NoConcatenate and stuff.

Change #2

Before the end of the loop, we concatenate the data from the [TMP] table into our new [EndTable Load] table. This is how that accumulation happens across the loop iterations.

Change #3

After the loop finishes, we Resident [EndTable Load] to get a new table called [EndTable] that includes our Where clause.

Change #4

Finally, now that we have our final table, [EndTable], we can delete the previous one, [EndTable Load], since we now no longer need it.