Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

andymanu
Contributor

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
New Contributor III

Re: Creating a variable for the folder path

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); 

24 Replies
gianpaolo_31
New Contributor III

Re: Creating a variable for the folder path

Maybe you can user OSUser() function.

Something like

vcurrUser = OSUser()


Employee
Employee

Re: Creating a variable for the folder path

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
Contributor

Re: Creating a variable for the folder path

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

Employee
Employee

Re: Creating a variable for the folder path

Hi Andy,

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

andymanu
Contributor

Re: Creating a variable for the folder path

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
Valued Contributor

Re: Creating a variable for the folder path

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);

Employee
Employee

Re: Creating a variable for the folder path

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
New Contributor III

Re: Creating a variable for the folder path

Hi Andy,

Use Let.

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

andymanu
Contributor

Re: Creating a variable for the folder path

Hi,


Still the LET function gives the same error.

Regards,

Andy

Community Browser