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

[andymanu]

Sorry, it should be "Hi Quy"

Apologies.

[Quy_Nguyen]

Hi,

"it is not loading the data" -> do you mean that there is no data in your data model?

If there is no data in your data model, you have to check step by step.

1.  Just run this code block, then check if data is loaded into your app:

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';  
Set vPathName = 'lib://Andy (XXXXX)/Data\Test_Path\';  
Temp:  
LOAD *  
FROM [$(vPathName)Products.xlsx]; 

2. After step 1, if you have the data in Temp table, run this code block:

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';  
Set vPathName = 'lib://Andy (XXXXX)/Data\Test_Path\';  

Temp:  
LOAD *  
FROM [$(vPathName)Products.xlsx];  

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

DROP TABLE Temp; 

3. After step 2, check the qvd file exists in the folder or not. If yes, try to load it from your data connection (open connection and select the qvd file), not by variable path.

4. If data is loaded successful from qvd file, try to modify the FROM clause by using variable path.

[gianpaolo_31 · Accepted solution]

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

[andymanu]

Hi Gian,

Yes its working

Thank you very much.

Only thing we were missing was,

"(ooxml, embedded labels, table is Products);" connection for the excel file.

Regards,

Andy

[andymanu]

Hi All,

Thank you very much everyone for your valuable support.

Only thing which was missing , "(ooxml, embedded labels, table is Products);"..

Thank you Lisa and Quy.

Regards,

Andy