Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andymanu
Creator II
Creator II

Creating a variable for the folder path

Hi,

I want to create a variable for the folder path so that, I could easily shift from one computer to another just changing the actual file path only in the variable statement.

I have seen the function of "SET" can be used to set the variable path for the folder the file is located (the file can be an excel file or a QVD file).

Find below the script to load the data in to qlik sense.

Temp1:

LOAD

    Prod_ID,

    Prod_Name,

    Quantity,

    Country,

    If(Country='NZ',1,0) as Flag1

 

FROM [lib://Andy (XXX)/Data\Sales_Data\Products.xlsx]

(ooxml, embedded labels, table is Products);

Could anyone provide a solution to accomplish the above said task since I tried some methods using SET function but did not give me the expected results.

Thanks in advance.

Kind regards,

Andy

1 Solution

Accepted Solutions
gianpaolo_31
Contributor III
Contributor III

Hi Andy,

This should work now.

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';   

Set vPathName = 'lib://Andy (XXXXX)/Data\Test_Path\';

Temp: 

LOAD * 

FROM [$(vPathName)Products.xlsx] 

(ooxml, embedded labels, table is Products);

STORE * FROM Temp INTO [$(vPathName)Temp.qvd]; 

DROP TABLE Temp; 

 

Table1: 

Load * 

FROM [$(vPathName)Temp.qvd] 

(qvd); 

View solution in original post

24 Replies
gianpaolo_31
Contributor III
Contributor III

Maybe you can user OSUser() function.

Something like

vcurrUser = OSUser()


Lisa_P
Employee
Employee

Hi Andy,

Declare the variable in the main section.  It's contents should match the connection name xxxxxxxxxxx

SET vPathName='lib://xxxxxxxxxxx/';

In the script where you want to apply the variable, you replace that part of the file path with

$(vPathName)

eg. FROM [$(vPathName)Products.xlsx]

andymanu
Creator II
Creator II
Author

Hi Lisa,

Thank you for your prompt reply.

I tried that, the code executes without any errors but will not load any data.

When I go to Data Model Viewer, it shows something like below,

Awaiting for your reply.

Thanks in advance.

Andy

Lisa_P
Employee
Employee

Hi Andy,

Can you show me a screenshot of the Data Load Editor where you load the table.

andymanu
Creator II
Creator II
Author

Hi Lisa,

Thank you very much for your support.

Note

Sorry I have put XXXX for the location only to protect the organization's file location.

please find the attached screenshot of the data load

Hope this information is sufficient for you.

Thanks.

Regards,

Andy

arvind1494
Specialist
Specialist

Hi Andy,


Fiirst set variable for the library name


Set vPath = 'lib://Andy (XXX)/Data\Sales_Data';


Now set that variable while storing qvd:

Table1:

Load *  FROM [lib://Andy (XXX)/Data\Sales_Data\Products.xlsx]


Store Table1 into [$(vPath)Products.qvd](qvd);

Lisa_P
Employee
Employee

That doesn't look right to me. You have a mix of forward and backward slashes. 

Try loading the data again by selecting the data from your connection.

Once you then have the correct library path after the from, copy and paste it into your vPathName variable.

gianpaolo_31
Contributor III
Contributor III

Hi Andy,

Use Let.

Let vOthers = 'lib://Andy (XXX)/Data\Sales_Data\Products.xlsx';

andymanu
Creator II
Creator II
Author

Hi,


Still the LET function gives the same error.

Regards,

Andy