Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
Hi Andy,
Set is best for storing a string.
I think the issue is still the path stored in your variable.
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
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);
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
Hi Andy,
I am sure that the problem here:
STORE * FROM Temp INTO [$(vPathName)Temp.qvd'];
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
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);
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