topic Re: Store global settings into a txt-file for include-statement in QlikView

Store global settings into a txt-file for include-statement

pacoli2013 — Mon, 15 May 2017 14:40:23 GMT

Hello,

We want to maintain the base of our Qlikviewdocument-settings on one central location, so in

our DWH we have created a file GlobalVariables with settings for those Qlikview Documents.

In a Qlikview Document (see attach document) I create a table GlobalVariables:

GlobalVariables:
LOAD
VariableName,
VariableContent,
VariableDescription;
SQL SELECT *
FROM "QlikView_Repository".dbo.GlobalVariables;

ToTXTfile:
NoConcatenate Load
VariableContent as Setting
Resident GlobalVariables;

This table ToTXTfile I store as a TXT-file:

Store ToTXTfile Into $(DataPathTxt)GlobalVariables.txt (txt, delimiter is ';');

The txt-file I get, I cannot use in an included statement like:

$(Include=c:\qlikview\datasources\txt\globalvariables.txt);

because I get load-failures and so the txt-file is not like expected:

Setting

"SET DataPathExcel = 'C:\Qlikview\Datasources\excel\';"

"SET DataPathTxt = 'C:\Qlikview\Datasources\txt\';"

"SET DataPathImages = 'C:\Qlikview\Datasources\images\';"

"SET DataPathQvd = 'C:\Qlikview\Datasources\qvd\';"

"SET DATADIR = 'E:\QlikView\Datasources\';"

"SET DataPathExcel = 'E:\QlikView\Datasources\excel\';"

"SET DataPathTxt = 'E:\QlikView\Datasources\txt\';"

"SET DataPathImages = 'E:\QlikView\Datasources\images\';"

"SET DataPathQvd = 'E:\QlikView\QVD_Files\';"

"LET vPeriodStartYear = (year(date(now()))-3);"

"LET vPeriodStartMonth = (month(date(now()))-3);"

"LET vPeriodStartMonthNum = num(month(date(now()))-3);"

"LET vPeriodStartDate = MakeDate((year(date(now()))-3), 1, 1);"

"LET vCurrentYear= (year(date(now())));"

"LET vCurrentMonth = (month(date(now())));"

"LET vCurrentMonthNum = num(month(today()));"

"LET vCurrentDateNum = num(today());"

"LET vPeriodEndYear = (year(date(now()))+2);"

"LET vPeriodEndMonth = (month(date(now()))+2);"

"LET vPeriodEndMonthNum = num(month(date(now()))+2);"

"LET vPeriodEndDate = MakeDate((year(date(now()))+2), 12, 31);"

"LET vMonthYear = (month(date(now()))) &'-'&(year(date(now())));"

"LET vInActiveCol = RGB(48/65/82);"

"LET vActiveCol = RGB (0/85/165) ;"

"LET vActiveColText = RGB (0/0/0) ;"

"LET vAccentCol = RGB (206/212/219);"

"LET #LANGUAGE = 'German';"

"LET vLanguage = '= Minstring(#LANGUAGE)';"

"LET vSelectedLangKey = Only(LangKey);"

"LET vCurrency = 'EUR';"

"LET vExcRate = 1;"

"LET vCurSign = Only(CurrencySign);"

"SET vOneOrder='=GetPossibleCount(SalesInvoiceNumber)=1';"

"SET vOneCustomer='=GetPossibleCount(CustomerNumber)=1';"

"SET vProductGroup='=GetPossibleCount(ProductGroup)=1';"

"SET vYearNumber='=GetPossibleCount(Year)=1';"

"SET vMonthNumber='=GetPossibleCount(MonthShortName)=1';"

"SET vMounting='=GetPossibleCount(Mountinggroup)=1';"

When I use the txt-file in n included statement and give a reload, the reload has problems with the VariableContent and "

My questions:

Is it possible to create a txt-file which can be used in an included statement?
When Yes: what should be changed in the store statement to get the right txt-file for the include statement?
When NO: is there another possible solution to get the settings from the DWH into a Qlikview Document and use them as Settings not as a Table?

Re: Store global settings into a txt-file for include-statement

prieper — Mon, 15 May 2017 14:51:12 GMT

you only need to erase the "s when loading.

REPLACE (Mytext, '"', '') ...

Peter

Re: Store global settings into a txt-file for include-statement

pacoli2013 — Tue, 16 May 2017 14:42:25 GMT

Hello Peter,

Your solution does not give the desired result.

I can load the created txt file as a normal text file and then I get a table of text without ", but if I load the file with the include command ($(Include=c:\qlikview\datasources\txt\globalvariables.txt);), I keep the error message, especially since the Label Setting is also loaded

And I have to use the inclue-load because the Settings are important for the document

Regards Court

Re: Store global settings into a txt-file for include-statement

flipside — Tue, 16 May 2017 15:32:30 GMT

The single quotes which appear in the field Setting cause the whole field value to be wrapped in double quotes causing the load problem. You could just load three fields - Action (LET or SET), Variable (Name), and Value as separate fields direct from the database table and iterate over the rows something like this (I'm using an xls file as a source) ...

GlobalVariables:

LOAD Action,

Variable,

Value

FROM [GlobalVariables.xls] (biff, embedded labels, table is Sheet1$);

For i = 0 to NoOfRows('GlobalVariables')-1

Let Variable = peek('Variable',$(i),'GlobalVariables');

Let Value = peek('Value',$(i),'GlobalVariables');

If peek('Action',$(i),'GlobalVariables') = 'LET' then

LET $(Variable) = $(Value);

ELSE

SET $(Variable) = $(Value);

ENDIF;

Next i;

Let i = Nothing;

Let Variable = Nothing;

Let Value = Nothing;

flipside

Re: Store global settings into a txt-file for include-statement

pacoli2013 — Wed, 17 May 2017 06:47:05 GMT

Hello David,

Thanks for your solution. I will test it and come back to you with the results

regards

Court