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]

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]

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]

Hi Andy,

Set is best for storing a string.

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

[andymanu]

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]

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]

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]

Hi Andy,

I am sure that the problem here:

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

[andymanu]

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]

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]

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_ID	Prod_Name	Quantity	Country
P100	A	30	USA
P101	B	20	NZ
P103	D	50	AUS
P101	B	35	NZ
P105	E	10	NZ
P104	F	20	SL
P106	G	30	AUS
P100	A	20	SL

Thank you.

Regards,

Andy