topic Re: Load script from excel in a sharepoint in Connectivity & Data Prep

Load script from excel in a sharepoint

ArmandP — Tue, 27 Dec 2022 14:26:53 GMT

Hello,

My company has given to me access for being able to develop things in Qlik but there are some features that I've not available. For example for the data prep I can only do it through script, and here is where I have some doubts:

I have an excel in a sharepint and I want to create a connection with it so every time I refresh, it's data gets refreshed too.

So far they have given me a sample script with what I can start:

$(Include=[lib://QS_Projects_BA_Standard_Data/10_Shared/20_Include/20_Functions/01_IT_Web_Connector/20_Specific/10_Sharepoint_O365/10_Files/GetFileQlikSelf_XXXqvs]);
LET Connection_3 = '';
LET File_3 = 'https://mycompany.sharepoint.com/sites/QlikSenseSelfServiceBA/Documents%20partages/XXX%20XXX/30_External%20source%20files/XXX/XXX_Daily_Data.xlsx';
LET Sheet_3='XXX_Accumulated';
LET Header_3 = 0;
CALL GetFileQlikSelf_XXX(Connection_3,File_3,Sheet_3,Header_3)

With this code what I've found when I run it is that the table name is the deafult excel name and the fields are named (excel name+ the column name).

I would like to have something similar to the following:

[New_Table]:

LOAD *

FROM route?

So I can upload the table directly with the name I want, instead of having to to another load this time with the correct table name and then drop the previous table:

RESIDENT [XXX_Daily_Data.xlsx];
DROP TABLE [XXX_Daily_Data.xlsx];

Also I don't know what's exacly doing the first part of the code:

$(Include=[lib://QS_Projects_BA_Standard_Data/10_Shared/20_Include/20_Functions/01_IT_Web_Connector/20_Specific/10_Sharepoint_O365/10_Files/GetFileQlikSelf_XXXqvs]);

Any help is welcome. Thank you very much!

Re: Load script from excel in a sharepoint

ArmandP — Wed, 28 Dec 2022 15:33:59 GMT

if someone has the same issue, I solved it quite simply:

$(Include=[lib://QS_Projects_BA_Standard_Data/10_Shared/20_Include/20_Functions/01_IT_Web_Connector/20_Specific/10_Sharepoint_O365/10_Files/GetFileQlikSelf_XXXqvs]);
LET Connection_3 = '';
LET File_3 = 'https://mycompany.sharepoint.com/sites/QlikSenseSelfServiceBA/Documents%20partages/XXX%20XXX/30_Exte...XXX_Daily_Data.xlsx';
LET Sheet_3='XXX_Accumulated';
LET Header_3 = 0;
CALL GetFileQlikSelf_XXX(Connection_3,File_3,Sheet_3,Header_3)

QUALIFY *;
[XXX_Export]:
LOAD *
Resident [XXX_Daily_Data.xlsx];
DROP TABLE [XXX_Daily_Data.xlsx];
UNQUALIFY *;

Basically I'm uploading the table, I'm creating a new one referencing the one I've already uploaded and adding the prefix with "Qaulify, unqualify" statement and dropping the first one.

Not very elegant as I wanted to do it all in one step but it works.

Best regards,

Re: Load script from excel in a sharepoint

nich_olas — Sun, 27 Aug 2023 02:06:35 GMT

Hi ArmandP, able to share the whole solution including the script for GetFileQlikSelf_XXX(Connection_3,File_3,Sheet_3,Header_3)? Thank you.