Incremental script help

[arusanah]

My requirement is to pull data for current FY. I'm trying to write a incremental Load Script -  If reload date fall in current FY year it should load previous months data except for july where FY year changes it should pull data for last month of previous year. e.g  if app reloads on 7/1/2024 it should pull data for jun2024( last month of previous FY ). I am having hard time writing this script. vUpdate variable doesnt return any value. Any help is much appreciated.

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//Section: Pre-defined Variables
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
SET vFiscalYearStartMonth = 7;

Let vPrevMonth= DATE(Monthstart(AddMonths(Today(),-1)),'YYYY/MM/DD');
Let vStartDate= Date(Yearstart(Today(),-1,'$(vFiscalYearStartMonth)'),'YYYY/MM/DD');

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//Section: Incremental Load
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

If '$(vLoad_Type)' = 'Full' or IsNull(QvdCreateTime('$(vQVD_Output_Target)')) Then

Let vUpdate_Date = Date(Yearstart(Today(),-1,'$(vFiscalYearStartMonth)'),'YYYY/MM/DD');

Else

Let vUpdate_Date =  if(TODAY()>= $(vPrevMonth),

                    //Limit to FYTD except July

                      if(InYear($(vPrevMonth),TODAY()-1, 0, 7)  //in same fiscal year

                          ,if(num(Month(TODAY()-1)) = 7 AND num(Month(DATE($(vPrevMonth)))) = 6 

                          AND InYear($(vPrevMonth), AddYears(TODAY()-1,-1), 0, 7);

;

[marksouzacosta]

Hi @arusanah ,

Not sure if you missed part of your code when you paste it here, but you are not closing any of your If statements and also missing some single quotes from the InYear. I also have removed some single quotes when they are not required - not a problem, just a style thing -  and have added some TRACE statements:

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//Section: Pre-defined Variables
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

SET vFiscalYearStartMonth = 7;

Let vPrevMonth= DATE(Monthstart(AddMonths(Today(),-1)),'YYYY/MM/DD');
TRACE vPrevMonth = $(vPrevMonth);

Let vStartDate= Date(Yearstart(Today(),-1,$(vFiscalYearStartMonth)),'YYYY/MM/DD');
TRACE vStartDate = $(vStartDate);

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//Section: Incremental Load
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
If '$(vLoad_Type)' = 'Full' or IsNull(QvdCreateTime('$(vQVD_Output_Target)')) Then
 Let vUpdate_Date = Date(Yearstart(Today(),-1,$(vFiscalYearStartMonth)),'YYYY/MM/DD');
Else
 Let vUpdate_Date =  if(TODAY() >= $(vPrevMonth),
                          if(InYear('$(vPrevMonth)',TODAY()-1, 0, 7),
                           if(num(Month(TODAY()-1)) = 7 AND num(Month(DATE('$(vPrevMonth)'))) = 6 AND InYear('$(vPrevMonth)', AddYears(TODAY()-1,-1), 0, 7),
                          'Result 1','Result 2'
                              ),'Result 3'
                          ),'Result 4');
End If

TRACE vUpdate_Date = $(vUpdate_Date);

Please let me know if that helps.

Regards,

Mark Costa