I am very new to qlik sense. I come from data warehouse background and we are using Qlik as our reporting/analytical platform. App we are trying to build is pretty big with lots of data and tons of expressions that we want to maintain at one place. Previous version of these formulas/expressions were kept in excel but there is read/write issues with multiple apps reading the same formula sheet from excel as excel has some limitations with read.
Solution was to put the formulas into a sql database table. If formulas/expressions change, we can change it in one place all they will propagate through all qlik apps upon reload.
here is my solution. I basically copied another person's version of code that loaded expressions from excel and replaced it with the sql table. Made lots of mistakes and whole LIB paths are new to me as well.
Here it is, i hope it helps someone.Took me a while to figure this out.
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
set vfolderPath = 'lib://test/' ;
//UPDATE THE "DB_CONNECTION" below to point to your database
LIB CONNECT TO 'DB_CONNECTION';
Expressions:
SQL select report_id as Variable, value as Expression from DB_CONNECTION..meta_qlik_formulas where isnull(value,'') <> '' and name like '%expression%' ;
Let vNumberOfRows = NoOfRows('Expressions');
For vI = 0 to (vNumberOfRows - 1)
Let vVariable_Name = Peek('Variable',vI,'Expressions');
trace $(vVariable_Name); \\output the variables to the screen to make sure they are going to load right.
Let [$(vVariable_Name)] = Peek('Expression',vI,'Expressions');
Next
