Hello the community,
I have a loop that checks during every reload if a new spreadsheet has been created.
If yes, the .xlsx file is uploaded and store into a .qvd
Now, sometimes, some existing spreadsheets are modified.
I'd like to add an IF statement to my script to take into account these modification before the QVD is created.
I guess I could just do a basic incremental load after my script, but I'm thinking about adding a nested if statement instead.
Below my current script
TRACE $(vDiv);
For Each v_File in FileList('$(RootDir_Data_Country_AR)*_monthlysummary.xlsx');
Let v_File_HoroDate = TEXTBETWEEN ('$(v_File)','\AR\','_monthly');
Let v_File_Folder = TEXTBETWEEN('$(v_File)','_Centers\','\AR\');
// TEST IF QVD EXISTS OR NOT
IF (isNull(qvdCreateTime('$(RootDir_FACT)monthlysummary_AR_$(v_File_HoroDate)_$(v_File_Folder).qvd'))) THEN
// IF DO NOT EXIST, ADD TO DATA TO BUILD LIST
List_AR_Country_Date_To_Build:
LOAD '$(v_File_HoroDate)' as AR_Country_Date_To_Build
AutoGenerate 1;
END IF
NEXT v_File
// TEST IF EXISITING DATA HAVE BEEN MODIFIED
????????????????????????
Let a = NoOfRows('List_AR_Country_Date_To_Build');
TRACE $(vDiv);
let l_Date_idx=1;
Do while len(fieldvalue('AR_Country_Date_To_Build',l_Date_idx))
let l_Date=fieldvalue('AR_Country_Date_To_Build',l_Date_idx);
TRACE $(l_Date);
AR_QUERY:
LOAD
Date#($(l_Date),'MM/DD/YYYY') as [Date],
Left(FileBaseName(),6) as [Period ID],
Textbetween('$(RootDir_Data_Country_AR)','_Centers\','\AR\') as SSC_Field,
RU_Code,
[Volume of ZAQ] as [AR Volume of ZAQ],
[Value of ZAQ] as [AR Value of ZAQ],
[Volume of ABC] as [AR Volume of ABC],
[Value of ABC] as [AR Value of ABC]
FROM
[$(RootDir_Data_Country_AR)$(l_Date)_monthlysummary.xlsx]
(ooxml, embedded labels);
STORE AR_QUERY into '$(RootDir_FACT)monthlysummary_AR_$(l_Date)_$(v_File_Folder).qvd';
DROP Table AR_QUERY;
let l_Date_idx=l_Date_idx+1;
LOOP
Where should I add an other IF statement to create a new QVD if the existing file has been modified?
If you have any idea...
Thank you.
