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

24 Replies
andymanu
Creator II
Creator II
Author

Hi,

I tried the below code using both "LET" and "SET".

However, still getting the connection error,

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

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

Temp:

LOAD *

FROM [$(vPathName)Products.xlsx];

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

DROP TABLE Temp;

Table1:

Load *

FROM [$(vPathName)Temp.qvd];

Appreciate, if you could let me know whether there is an error within the code.

Thanks all for your support.

Kind regards,

Andy

Quy_Nguyen
Specialist
Specialist

Hi,

I see the path for Product.xlsx file in the original post is different from the path you rely to Lisa's comment.

one is: lib://Andy (XXX)/Data\Sales_Data\Products.xlsx

other one is: lib://Andy (XXX)/Data\Test_Path\Products.xlsx

Try loading again by selecting Product.xlsx file from your Andy (xxx) connection and copy the script here.

Then every one can find the problem easier.

Lisa_P
Employee
Employee

Hi Andy,

Set is best for storing a string.

I think the issue is still the path stored in your variable.

andymanu
Creator II
Creator II
Author

Hi Quy,

Yes. The correct path is, lib://Andy (XXX)/Data\Test_Path\Products.xlsx


Sorry for stating a different one.


The script work correctly until the storing of "Temp.qvd" file. When I check the folder, i am able to see the Temp.qvd file.


The issue comes when it tried to load the data from the said Temp.qvd file to the Table1.


Regards,


Andy

gianpaolo_31
Contributor III
Contributor III

Hi Andy,

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;

Table1:

Load *

FROM [$(vPathName)Temp.qvd]

(qvd);

andymanu
Creator II
Creator II
Author

Hi Gian,

I tried adding (qvd) to the path as well.

But still giving an error while trying to load the stored data from the Temp.qvd file?

Thanks.

Regards,

Andy

Quy_Nguyen
Specialist
Specialist

Hi Andy,

I am sure that the problem here:

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

andymanu
Creator II
Creator II
Author

Hi Quy,

I am not sure. However, the Temp file get stored as a "qvd" file successfully.

Could be an issue with the file.

Regards,


Andy

Quy_Nguyen
Specialist
Specialist

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

You store the file by the name:  Temp.qvd' ->  there is an apostrophe here

Try this:

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;

Table1:

Load *

FROM [$(vPathName)Temp.qvd]

(qvd);

andymanu
Creator II
Creator II
Author

Ho Quy,

It was a great pickup.

Now the script is running without giving issues, but still it is not loading the data.

I'll copy the table I'm using for your reference.

   

Prod_IDProd_NameQuantityCountry
P100A30USA
P101B20NZ
P103D50AUS
P101B35NZ
P105E10NZ
P104F20SL
P106G30AUS
P100A20SL

Thank you.


Regards,

Andy