topic Re: Reading file as it is available from SharePoint Library in QlikView

Reading file as it is available from SharePoint Library

Wed, 06 Nov 2013 11:10:34 GMT

I need to load an excel file from SharePoint Library to Qlikview. This file is uploaded once monthly and has no fixed date of uploading.

How can I load this file every month as soon as it is uploaded on sharepoint site?

Thanks,

Pallav

Re: Reading file as it is available from SharePoint Library

salto — Wed, 06 Nov 2013 11:13:49 GMT

A very simple approach should be to schedule the reload every day at a certain hour. If both documents have the same name and the new document is still not available it will load the old data. As soon as it is available it will update the QV document with the Excel file.

Hope this helps.

Re: Reading file as it is available from SharePoint Library

Wed, 06 Nov 2013 11:41:01 GMT

Hello Salto,

This is a once-monthly arriving file with filename_month_year.xls kind of format. So, all the files would be of different names. And if on sharepoint the file does not exist, script throws an error and reload gets failed on QVworkbench,

Is there any way, I can only reload the file when it is available or skip that reloading part when it is unavailable on sharepoint there.

Or can we make the reload status as 'success', if it does not find the file and reloads all other parts successfully?

Thanks,

Pallav

Re: Reading file as it is available from SharePoint Library

salto — Wed, 06 Nov 2013 12:40:08 GMT

would try setting two variables in the load script:

Let CurrentYear = Year(Today());

Let CurrentMonth = Month(Today());

Let vFilename= 'YourFileName_' & CurrentMonth & '_' & CurrentYear & '.xls';

Load * from [htp://mysharepointsite/..../$(vFilename)] (ooxml, ..., table is...)

If we can pass the filename as a parameter, QVwill try loading it. If it fails (because a file with such a name does not exist) it will show the last successful load.

Hope this helps

Re: Reading file as it is available from SharePoint Library

Wed, 06 Nov 2013 14:08:44 GMT

Hello Salto,

I had tried passing the file name as parameter itself- dynamically forming the name. But if it does not find that file, it throws that error which causes the 'reload failure' status on qvworkbench. Although I am loading all the other data sccessfully.

I am trying to avoid that error itself, if the file is not available- do not reload it. Is that possible?

Thanks,

Pallav

Re: Reading file as it is available from SharePoint Library

salto — Wed, 06 Nov 2013 14:19:04 GMT

Hello Pallav,

then I assume that if the file exists, it finds it when we dinamically form the name? Good!

If the file does not exist, we change the value of the ErrorMode variable to 0,

which prevents QV from stopping the script execution if an error occurs.

We then try to load the table and afterwards check the variable ScriptError to see if an error has occurred.

If ScriptError has a value of 1 then no error occurred.

A value of 8 denotes that the error that occurred was that the file does not exist. We can replace the TRACE statements with the relevant actions to take. We then set ErrorMode back to it's default value of 1 and continue the rest of the script (thanks Matthew Fryer for the help!)

SET ErrorMode = 0;

Load * from [htp://mysharepointsite/..../$(vFilename)] (ooxml, ..., table is...)

IF ScriptError>1 AND ScriptError<>8 THEN

TRACE

//Load previous month

Let YearToLoad = Year(Today())-1;

Let MonthToLoad = Month(Today())-1;

Let vFilename= 'YourFileName_' & MonthToLoad & '_' & YearToLoad & '.xls';

ELSE

TRACE File loaded;

END IF

SET ErrorMode = 1;

Hope this helps!

Re: Reading file as it is available from SharePoint Library

salto — Thu, 07 Nov 2013 07:19:34 GMT

Hello Pallav,

did you have the chance to try this approach? It would be great to know if it worked.

Regards.

Re: Reading file as it is available from SharePoint Library

Thu, 07 Nov 2013 07:34:42 GMT

Hey Salto,

I already was trying something like this(did not use the exact error code earlier). It completes the reload but in qvworkbench it still says reload failure.

I am unable to avoid that message.

Thanks,

Pallav

Re: Reading file as it is available from SharePoint Library

Thu, 07 Nov 2013 07:46:19 GMT

This is what I was trying to do, Salto.

Let vPrevMonth= Month(today()-1);

SET errormode=0;

sharepoint_data:

Load * from [http://sharepointsite..](ooxml...); // loading last month data dynamically chaning the filename

if Scripterror>1 and Scripterror=12 then // 12 is the error code I get (wrong format)

history_data:

Load * from history.qvd(qvd);

else

SET errormode=2;

history:

concatenate load * from history.qvd(qvd);

store history into history.qvd; // remaking the history qvd

Thanks,

Pallav