topic Re: Dynamic File path in QlikView

Dynamic File path

kevincagle — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I want QlikView to use the file path based on the location of the QV file. I have a large volume of identical folders on various servers that collect reporting data for multiple inventory items, categorically separated. The file name of the report will be the same in each folder, essentially static. The only change will be the path to the file, essentially dynamic. I want the QV file to load the static file from the dynamic path it is located. My resolve will be to drop a copy of the QV file into each report folder. I will later work on an index that will make this an easier process but for now a solution for the above will work.

Here is what I'm using to load multiple sheets from an single excel database. What format does [...inventory report.xlsx] need to be in to accomplish this?

Data:
LOAD Store,
Location,
Year,
Month,
Category,
Subcategory,
Volume

FROM
[...inventory report.xlsx]
(ooxml, embedded labels, table is Volumes);

Concatenate(Data)
Load Year,

Store,
Month,
Location

From
[...inventory report.xlsx]
(ooxml,embedded labels, table is Rack);

Re: Dynamic File path

m_woolf — Fri, 20 Apr 2018 14:25:25 GMT

Look at Help Example 2:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptControlStatements/For%20Each…

Re: Dynamic File path

jonvitale — Fri, 20 Apr 2018 14:35:52 GMT

This is how I do it:

let path = 'lib//myConnection/myPath';
...
FROM [$(path)/theRestOfMyPath/theFilename.QVD]

Re: Dynamic File path

m_woolf — Fri, 20 Apr 2018 19:50:27 GMT

If the Excel doc will be in a subfolder:

from [.\FolderName\inventory report.xlsx]

Re: Dynamic File path

kevincagle — Thu, 03 May 2018 18:55:03 GMT

Is THEN part of the syntax or a statement? Im fairly new to the Qlik language/syntax but familiar with others.

let path = 'lib//myConnection/myPath';

FROM [$(path)/theRestOfMyPath/theFilename.QVD]

let path = 'lib//myConnection/myPath';

THEN

FROM [$(path)/theRestOfMyPath/theFilename.QVD]

Re: Dynamic File path

jonvitale — Thu, 03 May 2018 19:48:01 GMT

The former,

Sorry, I should have used color coding. The "then" was just part of my explanation, not Qlik code.