topic Re: Check if sheet has been modified before create QVD - nested If - qvdCreatetime in QlikView

Check if sheet has been modified before create QVD - nested If - qvdCreatetime

Wed, 10 Jun 2015 18:59:51 GMT

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.

Re: Check if sheet has been modified before create QVD - nested If - qvdCreatetime

stevedark — Wed, 10 Jun 2015 19:23:32 GMT

The best way to check the date on the file is FileTime. You probably want to do a compare between the time on the source spreadsheet and the last created QVD.

This blog post gives some examples of ways you can effciently handle a folder of source files and move data into QVDs, only refreshing changed files:

http://www.quickintelligence.co.uk/convert-drop-folder-files-qvd/

The key line of code from the post is:

if alt(FileSize('$(vQVD)'), 0) = 0 or FileTime('$(vQVD)') < FileTime('$(vFile)') then

Where vQVD is the output file and vFile is the source file.

Hope it helps.

Steve

Re: Check if sheet has been modified before create QVD - nested If - qvdCreatetime

Wed, 10 Jun 2015 20:51:31 GMT

Thank you Steve

This post is indeed very useful.

I tried to implement the code to my script but I'm still struggling to make it works, I'm still very new to Qlikview.

It says Script line error : Next v_File

By any chance, do you see what would be my error on the script below ?

// 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 QVD HAS BEEN MODIFIED USING FILETIME
IF (alt(FileTime('$(RootDir_FACT)monthlysummary_AR_$(v_File_HoroDate)_$(v_File_Folder).qvd') < FileTime('$(RootDir_Data_Country_AR)*_monthlysummary.xlsx'))) THEN
// IF modified, ADD TO DATA TO BUILD LIST
List_AR_Country_Date_To_Build:
LOAD '$(vQVD)' as AR_Country_Date_To_Build
AutoGenerate 1;
END IF
NEXT v_File

thanks

Re: Check if sheet has been modified before create QVD - nested If - qvdCreatetime

stevedark — Wed, 10 Jun 2015 21:32:39 GMT

You don't need the v_File after the NEXT statement, so you can take those out.

It's hard to see with the indentation as is, but it appears that you have two NEXT statements and only one FOR?

I think the NEXT that is half way down the script needs to come out?

Re: Check if sheet has been modified before create QVD - nested If - qvdCreatetime

Thu, 11 Jun 2015 16:11:27 GMT

Steve,

I just duplicated my script and changed the IF statement from one tab to the other with

IF (alt(FileTime('$(RootDir_FACT)monthlysummary_AR_$(v_File_HoroDate)_$(v_File_Folder).qvd') < FileTime('$(RootDir_Data_Country_AR)*_monthlysummary.xlsx'))) THEN

It works just fine now.

Thanks a lot for the help