Creating a variable for the folder path

[andymanu]

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

[gianpaolo_31]

Maybe you can user OSUser() function.

Something like

vcurrUser = OSUser()

[Lisa_P]

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]

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]

Hi Andy,

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

[andymanu]

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]

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]

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]

Hi Andy,

Use Let.

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

[andymanu]

Hi,

Still the LET function gives the same error.

Regards,

Andy