topic Re: Creating a variable with variables within - let Var 3=$(var2)$(var1) in QlikView

Creating a variable with variables within - let Var 3=$(var2)$(var1)

Anonymous — Thu, 12 Apr 2018 19:39:24 GMT

Hi all,

Is it possible to create a variable with a variable in it? EXAMPLE: Let Var 3 = $(Var2)($Var1)

Here is some background:

I am creating (trying to create) a loop-script that will:

First identify the existing excel docs in a folder (Ex. Doc 1, Doc 2, Doc 3 all in Folder1) - the table I created reads out the Document Name (=FileName) and the Last Modified Date (=FileTime)
I then order the documents based on 'last modified date' via a row count (for some reason Qlik wouldnt let me order by my date field)
Then, I created a new table which uses the Peek function to steal the top records details (aka the most recently modified records)
Dropped the original tables
I created a variable called vFileName_LastModified which pulls the document name of the most recently modified document from the final standing table
I plan to then create another variable which will tie together the folder pathway (already exists as a variable vFolderPath) and the Document Name variable (vFileName_LastModified) --- is this possible?

let vFolderPath = 'L:\San Francisco\Op Docs\Negotiations\Business Management\Reports\Salesforce\Terms\Data Dictionary\Qlikview Load\';
let vFileName_LastModified = peek('File Name',0,'LMD');

Let vFileName = ($(vFolderPath)$(vFileName_LastModified)); #This is the variable I need help on. It is used at the bottom of my script to tell Qlik where to pull the data from

[File Details]: #pulls in all the details of each file in the folder
Load
Date(Filetime(),'MM/DD/YYYY hh:mm:ss tt') as File_Last_Modified_Dates,
FileName() as File_Name,
RowNo() as [Row Count]
from $(vFolderPath)DataDictionary_wPublishConfig_*; #Qlik doesn't allow a Order By or Where clause here so I had to create a resident table which would

[Doc Details]: #reformats the data so I can sort is with the Order By function
Load
[Row Count],
File_Name as [File Name],
File_Last_Modified_Dates as [Last Mod. Document]
Resident [File Details] order by [Row Count] desc; #Couldn't use a where clause on the [Last Mod. Document] field so I resorted to [Row Count] per the natural order of modified date

[LMD]: # Keeps only the details of the last modified document
Load
distinct(peek('File Name',0,'Doc Details')) as Name,
peek('Last Mod. Document',0,'Doc Details') as Date
resident [Doc Details];

Drop table [File Details];
Drop table [Doc Details];
__________________________________________________________________________________

[Agreement Terms]:
LOAD *
FROM # I want to change the load statement to vFileName

(ooxml, embedded labels, table is [Agreement Terms]);

join ('Agreement Terms')
[Downgrade WF]:
LOAD *
FROM # I want to change the load statement to vFileName

(ooxml, embedded labels, table is [Downgrade WF]);

Re: Creating a variable with variables within - let Var 3=$(var2)$(var1)

sasiparupudi1 — Thu, 12 Apr 2018 19:52:07 GMT

Let vFileName = '$(vFolderPath)$(vFileName_LastModified)';

[Agreement Terms]:
LOAD *
FROM # I want to change the load statement to vFileName
['$(vFileName )']
(ooxml, embedded labels, table is [Agreement Terms]);

Re: Creating a variable with variables within - let Var 3=$(var2)$(var1)

Anonymous — Thu, 12 Apr 2018 19:58:43 GMT

This the return statement:

Cannot open file ''L:\San Francisco\Op Docs\Negotiations\Business Management\Reports\Salesforce\Terms\Data Dictionary\Qlikview Load\'' The filename, directory name, or volume label syntax is incorrect.

[Agreement Terms]:
LOAD *
FROM ['L:\San Francisco\Op Docs\Negotiations\Business Management\Reports\Salesforce\Terms\Data Dictionary\Qlikview Load\']

(ooxml, embedded labels, table is [Agreement Terms])

It looks like Qlikview is only reading the first Variable (vFolderParth) and leaving out the document address (vFileName_LastModified).

Thoughts?

Re: Creating a variable with variables within - let Var 3=$(var2)$(var1)

sasiparupudi1 — Thu, 12 Apr 2018 20:06:38 GMT

[Doc Details]: #reformats the data so I can sort is with the Order By function
Load
[Row Count],
File_Name as [File Name],
File_Last_Modified_Dates as [Last Mod. Document]
Resident [File Details] order by [Row Count] desc; #Couldn't use a where clause on the [Last Mod. Document] field so I resorted to [Row Count] per the natural order of modified date

let vFileName_LastModified = peek('Last Mod. Document',0,'Doc Details') ;
Drop table [File Details];
Drop table [Doc Details];

Let vFileName = '$(vFolderPath)$(vFileName_LastModified)';

[Agreement Terms]:
LOAD *
FROM # I want to change the load statement to vFileName
['$(vFileName )']
(ooxml, embedded labels, table is [Agreement Terms]);

Please check the app log file to see if the vFileName_LastModified shows the required data in it?

Re: Creating a variable with variables within - let Var 3=$(var2)$(var1)

Anonymous — Thu, 12 Apr 2018 20:18:41 GMT

The return from your suggestion was (as is below) -- it's in the right track as now the doc name is present in the full address.

Cannot open file ''L:\San Francisco\Op Docs\Negotiations\Business Management\Reports\Salesforce\Terms\Data Dictionary\Qlikview Load\DataDictionary_wPublishConfig__4_12_18.xlsx'' The filename, directory name, or volume label syntax is incorrect.

[Agreement Terms]:
LOAD *
FROM ['L:\San Francisco\Op Docs\Negotiations\Business Management\Reports\Salesforce\Terms\Data Dictionary\Qlikview Load\DataDictionary_wPublishConfig__4_12_18.xlsx']

(ooxml, embedded labels, table is [Agreement Terms])

I removed the ' ' form ['$*vFileName'] and so that it would return

Seemed to work better but I now have a new problem: Error File extdata.pp, line 2278 - I found this post and am not happy with what it says...

File extdata.cpp, Line 2278 Error

Re: Creating a variable with variables within - let Var 3=$(var2)$(var1)

sasiparupudi1 — Thu, 12 Apr 2018 20:23:38 GMT

what if you try

[Agreement Terms]:
LOAD *
FROM
'$(vFileName )'
(ooxml, embedded labels, table is [Agreement Terms]);

Re: Creating a variable with variables within - let Var 3=$(var2)$(var1)

Anonymous — Thu, 12 Apr 2018 20:29:36 GMT

I tried... '$(vFileName )' and $(vFileName )

Both returned, sadly:

Error: File extdata.cpp, Line 2278
[Agreement Terms]:
LOAD *
FROM L:\San Francisco\Op Docs\Negotiations\Business Management\Reports\Salesforce\Terms\Data Dictionary\Qlikview Load\DataDictionary_wPublishConfig__4_12_18.xlsx

(ooxml, embedded labels, table is [Agreement Terms])

Re: Creating a variable with variables within - let Var 3=$(var2)$(var1)

Anonymous — Thu, 12 Apr 2018 20:35:04 GMT

Do you think it would be possible to hard code the folder location (would is now FolderPath) and only include the file variable (vFileName_LastModified)? Ill give it a go and let you know...

Example:

L:\San Francisco\Op Docs\Negotiations\Business Management\Reports\Salesforce\Terms\Data Dictionary\Qlikview Load\$(vFileName_LastModified)