Dimensions and calculations over periods are essential parts from nearly each reporting. The analysis from data regarding to periods is quite independent from the to analyse data-area regardless if this are sales-, finance-, human-ressources- or production-data. Nearly nothing is more exciting within an analysis as the development from data over the time and the questions which are following like: Was these development expected or not and what could be the reasons?

However the handling from time-data could be difficult whereas the most could be avoided with a few simple rules.

The easiest way is often to use a master-calendar as dimension-table which is linked to the fact-table(s). Why and how, see:

Use of Master Calendar

The Master Calendar

The Master Time Table

The Fastest Dynamic Calendar Script (Ever)

Master Calendar with movable holidays

In more complex data-models is it often necessary to create several calendars and/or to use calendars which are divergent to normal year-calendars.

Canonical Date

Why You sometimes should Load a Master Table several times

Fiscal Year

Fiscal Calendar with Non-Standard Days (Not 1-31)

4-5-4 Master Calendar‌

Non-Gregorian calendars

Redefining the Week Start

Redefining the Week Numbers

Calendars

Important is to define and formate the time-dimension properly. Properly meant that the dimensions are (also) numeric then only numeric values could be calculated respectively compared with each other.

Background is that the date of 12/31/1899 is equal to 1 and each further day will be added by one so that the date of 12/31/1999 corresponds to 36525. Hours/Minutes/Seconds are fractions from 1, for example 1 / 24 / 60 = 0,000694 is equal to 1 minute.

This meant that all fields which should be calculated (comparing is calculation, too) should be (additionally) available as numeric field or as Dual-Field:

Get the Dates Right

The Date Function

Why don’t my dates work?

Often are additionally relative and/or continuing time-dimensions and flags very helpful to avoid complex calculations:

Relative Calendar Fields

Creating Reference Dates for Intervals

Calendar with period flags

Calendar with flags making set analysis so very simple‌

Period Presets: Compare Periods on the fly

The As-Of Table

Subroutine to Create Data Model for From/To Date Selection‌

Calendar with AsOf Flags, Compare Easter to Easter

Beside simple but frequent time-comparing with one or several time-dimensions in one object and simple expressions like sum(value) or count(order) are there more complicated questions like:

Year-over-Year Comparisons

Previous YTQ, QTD, MTD and WTD

Calculating rolling n-period totals, averages or other aggregations

Beside the above used links you will find many interessting postings here within the qlik community to these topic - the notes here are a good starting point to go further.

Have fun!

Marcus Sommer

ps: within the attachment is a german translation - deutsche Fassung.

Inspired through this question: General Q. and the solution which tresesco provided in the sub-link: how to convert number into words I thought that must be (easier) possible with QlikView itself. At first I considered to split the number with a while-loop and catch to each single-number then a mapping-value. But because of the slight inhomogeneity from the spelling words to the numbers it would be some checking with if-loops necessary. Therefore I deceided to make it more simple. It needs only two small mapping-tables and a preceeding-load in which each word-part oft he number will be separate generated and afterwards simple string-concatenated and a little bit cleaned.

This example is restricted to numbers < 1M but the logic could be extended. I hope I have caught every possibility by the translation - but don't hesitate if you find a error or if you have ideas for improvements.  And this is the result:

Map_FirstNumbers:
mapping Load * Inline [
FirstNumbers, Text
1, one
2, two
3, three
4, four
5, five
6, six
7, seven
8, eight
9, nine
10, ten
11, eleven
12, twelve
13, thirteen
14, fourteen
15, fifteen
16, sixteen
17, seventeen
18, eighteen
19, nineteen
20, twenty
];

Map_SecondNumber:
mapping Load * Inline [
SecondNumber, Text
2, twenty
3, thirty
4, fourty
5, fifthy
6, sixty
7, seventy
8, eighty
9, ninety
];

NumbersInWords:
load *, capitalize(trim(replace(NumbersInWordsRawFormat, Repeat(Chr(32), 3), chr(32)))) as NumbersInWords;
load *, [6 value] & [6 unit] & [4+5 value] & [4-6 unit] & [3 value] & [3 unit] & [1+2 value] as NumbersInWordsRawFormat;
load *,
if(right(Number, 2) < 21, applymap('Map_FirstNumbers', right(Number, 2), chr(32)),
if(right(Number, 2) < 100, applymap('Map_SecondNumber', mid(right(Number, 2), 1, 1), chr(32)) & chr(32) &
applymap('Map_FirstNumbers', mid(right(Number, 2), 2, 1), chr(32)))) as [1+2 value],
if(LenNumber >= 3, applymap('Map_FirstNumbers', num#(mid(right(Number, 3), 1, 1)), chr(32)), chr(32)) as [3 value],
if(LenNumber >= 3 and num#(mid(right(Number, 3), 1, 1)) > 0, ' hundred ', chr(32)) as [3 unit],
if(match(LenNumber, 4, 5, 6),
if(LenNumber = 4, applymap('Map_FirstNumbers', num#(mid(right(Number, 4), 1, 1)), chr(32)),
if(num#(mid(right(Number, 5), 1, 2)) < 1, chr(32), if(num#(mid(right(Number, 5), 1, 2)) < 21,
applymap('Map_FirstNumbers', num#(mid(right(Number, 5), 1, 2))),
if(num#(mid(right(Number, 5), 1, 2)) < 100, applymap('Map_SecondNumber', num#(mid(right(Number, 5), 1, 1)), chr(32)) & chr(32) &
applymap('Map_FirstNumbers', num#(mid(right(Number, 4), 1, 1)), chr(32)))))), chr(32)) as [4+5 value],
if(LenNumber >= 5, ' thousand ', if(LenNumber = 4 and num#(mid(right(Number, 4), 1, 1)) > 0, ' thousand ', chr(32))) as [4-6 unit],
if(LenNumber = 6, applymap('Map_FirstNumbers', num#(mid(right(Number, 6), 1, 1)), chr(32)), chr(32)) as [6 value],
if(LenNumber >= 6 and num#(mid(right(Number, 6), 1, 1)) > 0, ' hundred ', chr(32)) as [6 unit];
load recno() as Number, len(recno()) as LenNumber Autogenerate 999999;

Marcus_Sommer

Hi All,

This document helps you in loading multiple excels and excel sheets with the name of the sheet and data.

//to read each file from the specified folder

FOR EACH file in FileList('filepath\*.xlsx');

//In order to get the file information from SQLtables command making use of the ODBC connection format
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:
SQLtables;
DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
Table:
Load * ,
FileBaseName()as FIle,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT i

Next

Hope this helps !!!

please find the attachment for the eg: qvw and test fiels

Regards,

Avinashelite‌

Working hours calculation is essential measure which is useful in many business cases.  In this post I am going to demonstrate the steps to calculate this measure.  I have also covered other aspects related to working hours like overtime hours and excluding lunch break hours.  

Example in this post works for weekends as Saturday and Sunday. If you have any other weekends then refer to my below post

Working-days-and-hours-calculations-for-custom-weekends

Consider the below case

Let's say one Ticket is logged into the system on certain day , call it as Start Date, and the same Ticket got resolved on certain day, call it as End Day. Now we may want to calculate the working hours between these two Dates to calculate the efficiency of ticket closure time.

1) Calculate business working hours excluding week ends(Saturday and Sunday) and Holidays.

Below are the considerations

1) Count only standard working hours ( 9 AM - 6 PM)

2) Exclude Saturdays and Sundays

3) Exclude Holidays

Below is the Setup in QlikView

You can change the Date format of the below variables as per your convenience eg. 'DD/MM/YYYY hh:mm:ss' to 'DD-MMM-YYYY hh:mm:ss'

SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss';

First, convert your Timestamp date format to 24 hour format using the below functions

a) Timestamp# ()    (if your Timestamp values are text) 

b) TimeStamp ()     (If your Timestamp values are in proper Timestamp format)

Now you can setup the below variables for standard working hours in weekdays.You can change the Variable according to your working hours (Here 9 AM - 6 PM) and rest calculation will be done automatically

Now Setup the Holiday list as below. You can use your own holiday list

Below is the logic to calculate the business working hours between to dates

Here is the output

2) Calculate business working hours excluding week ends(Saturday and Sunday), Lunch Breaks and Holidays.

Below are the considerations

1) Count only standard working hours ( 9 AM - 6 PM)

2) Exclude Saturdays and Sundays

3) Exclude Lunch Break (1 PM - 2PM)

4) Exclude Holidays

Set the Variables for standard working hours and lunch breaks. You can change the values according to your needs

Include the Holidays

Below is the logic to calculate the business working hours between two dates

Here is the output

If you want to present the working hours in hh:mm:ss format then you can use below logic after calculating working hours

=interval(Working_Hours/24,'hh:mm:ss')

Please see the attached QVW.

Feel free to provide any suggestions.

Hi Folks,

If any one interest to freeze all the data by month wise from sql and append it in the qvd to verify the changes per each month. It will help it.

Let vQVDPath = 'D:\';

Let vFreezeTableName = 'MONTH_FREEZE';

LET vNow = Now();

//GFIR

$(vFreezeTableName):

LOAD *,

  '$(vNow)' AS MonthFreezeTime,

  Floor(Today()) AS MonthFreezeDate,

  Floor(MonthEnd(Today(), -1)) AS MonthFreezeDataUptoDate;

SQL Select * from tablename;

//Check the qvd is exists or not

  LET vListQVDExists = not isnull(QVDCreateTime('$(vQVDPath)\$(vFreezeTableName).qvd'));

  //If exists , concatenate with existing qvds

  If($(vListQVDExists)) then

  //Find the maximum date

     MaxFreezeDate:

     LOAD Max(MonthFreezeDate) AS MaxFreezeDateNum Resident $(vFreezeTableName);

     //Get the maximum date_num

     Let vMaxFreezeNum = Num(Peek('MaxFreezeDateNum',-1, 'MaxFreezeDate'));

     //Drop the temporary table

     DROP Table MaxFreezeDate;

  Concatenate($(vFreezeTableName))

  LOAD * From

  $(vQVDPath)\$(vFreezeTableName).qvd(qvd)

  Where MonthFreezeDate <  $(vMaxFreezeNum);

  ENDIF;

//Store freeze data into qvd

STORE $(vFreezeTableName) into $(vQVDPath)\$(vFreezeTableName).qvd(qvd);

//Drop the temporary table

DROP Table $(vFreezeTableName);

//Exit script

EXIT Script;

This article is about converting the GK (Gauss-Krüger) coordinates into WGS 84 (Latitude/Longitude)

Background:

In cartography, the term Gauss–Krüger Geographic coordinate system is named after Carl Friedrich Gauss (1777-1855) and Johann Heinrich Louis Krüger. It is a particular set of transverse Mercator projections (map projections) used in narrow zones in Europe and South America, at least in Germany, Turkey, Austria, Slovenia, Croatia, Macedonia, Finland and Argentina. This Gauss–Krüger system is similar to the universal transverse Mercator system (UTM), but the central meridians of the Gauss–Krüger zones are only 3° apart, as opposed to 6° in UTM. Depending on the zone of the Gauss-Kruger coordinates, different EPSG codes are applicable (The country of Germany is within the EPSG-Codes 31466 to 31469). Nevermind about the EPSG code, the below conversion works on any Gauss-Kruger coordinates.

The typical Gauss-Krüger coordinates is a pair of "Rechtswert" (East) and "Hochwert" (North) coordinates, both numbers are 7 digits long, may have up to 3 decimals (since the unit is meter, decimals don't make much sense as they are about decimeters, centimeters, even millimeter).

Links:

• An online translater example is here:  WGS84 L�nge/Breite oder Gau�-Kr�ger nach MGRS :: Geograph Deutschland
  It supports even http-get requests.
• I would not have been able to solve this without this How-To Excel. http://www.kram.de/pca/koordinatenumrechnung.xls Thanks to the author!
• http://en.wikipedia.org/wiki/Gauss%E2%80%93Kr%C3%BCger_coordinate_system
• DE:Gauß-Krüger - OpenStreetMap Wiki (in German)

Solution:

Enough about the background, to convert given set of Gauss-Krüger coordinates from a given "Rechtswert" (East) and "Hochwert" (North) in QlikView add the following script fragments. Adjust the values of XCOORD_Field and Y_COORD Field with the effective column names in which the GK values are found in the later LOAD statement.

// constants

LET XCOORD_Field = 'XKOORD';

LET YCOORD_Field = 'YKOORD';

// Formulas
SET @rho = 57.29577951; //=180/PI

SET @e2 = 0.006719219;

SET @b1 = "($(YCOORD_Field)/10000855.7646)";

SET @b2 = "Pow($(@b1),2)";

SET @bf = "325632.08677*$(@b1)*((((((0.00000562025*$(@b2)-0.0000436398)*$(@b2)+0.00022976983)*$(@b2)-0.00113566119)

    *$(@b2)+0.00424914906)*$(@b2)-0.00831729565)*$(@b2)+1) / 3600/ $(@rho)";

SET @fa = "(($(XCOORD_Field)-(Floor($(XCOORD_Field)/1000000)*1000000)-500000)/(6398786.849/Sqrt(1+(Pow(COS($(@bf)),2)*$(@e2)))))";

SET @LAT_Formula = "($(@bf)-(Pow($(@fa),2)*(Sin($(@bf))/Cos($(@bf)))*(1+(Pow(COS($(@bf)),2) * $(@e2)))/2)

    +(Pow($(@fa),4)*(Sin($(@bf))/Cos($(@bf)))*(5+(3*Pow(Sin($(@bf))/Cos($(@bf)),2))+(6*(Pow(COS($(@bf)),2)

    * $(@e2)))-(6*(Pow(COS($(@bf)),2) * $(@e2))*Pow(Sin($(@bf))/Cos($(@bf)),2)))/24)) * $(@rho)";

SET @LON_Formula = "(($(@fa)-(Pow($(@fa),3)*(1+(2*Pow(Sin($(@bf))/Cos($(@bf)),2))+(Pow(COS($(@bf)),2)

    * $(@e2)))/6)+(Pow($(@fa),5)*(1+(28*Pow(Sin($(@bf))/Cos($(@bf)),2))+(24*Pow(Sin($(@bf))/Cos($(@bf)),4)))/120))

    * $(@rho)/COS($(@bf))) + (Floor($(XCOORD_Field)/1000000)*3)";

Now if you import a file or table with Gauss-Krüger coordinates in fields XKOORD / YKOORD this is your script (dark-blue part). If you plan to use QlikView's built-in Mapping Extension "Quick Map" or "QlikView Mapping Extension", the coordinates-pair needs to go into one field, which I am calling LON_LAT. They need to have US number format. use the dark-red part of the script as well.

SET US_Format = "'','.',' '";

LOAD

    *

    ,Num(LON,$(US_Format)) & ',' & Num(LAT,$(US_Format)) AS LON_LAT   

;

LOAD

     ID

     ,XKOORD,

     ,YKOORD

     ,$(@LAT_Formula) AS LAT

     ,$(@LON_Formula) AS LON

FROM

    [myExcel.xlsx]

    (ooxml, embedded labels);

Enjoy,

Christof

How could I make sure that certain data are only for certain users available?

The answer is SECTION ACCESS. Section access is a connection between an authorization table (commonly placed in a hidden script and/or outsourced in an include-variable) and the application data (most to a dimension table).

Further you need to enable section access within the document properties in tab open and especially to enable the strict exclusion is important then without this is section access more a comfortable usability function to avoid confusing by the users which needs only a small data-area from the application but not more suitable for real confidential data.

Introduction to Section Access

A Primer on Section Access

Data Reduction – Yes, but How?

Section Access: Strict Exclusion

QlikView Section Access Examples

Data Reduction Using Multiple Fields

Video - Section Access best practice

Section Access (Data Reduction) using Active Directory

Between desktop and server are differences which you should consider: Section Access: Desktop vs Server.

In addition to these there are more complex restrictions possible but before you build one think on alternatives like Document chaining whereby those applications then have a specialized and simpler section access.

Basics for complex authorization

Authorization using a Hierarchy

Restrictions based on section access could be applied on sheets and objects.

Sheets Security with Section Access

Sheets Security with Section Access File

Section Access - Hide Sheet Level

Chart Level Access by user

Sheet level access

Sometimes there is a need to mask or de-identify data for certain users, for this see: Mask or de-identify data for certain users using Section Access.

At least the most important remark: before thinking on section access makes one or more BACKUP's from your applications then there aren't any go backs by mistakes! One exception (which don't nullified the suggestion of having Backup's) could you find here: Bypassing QlikView Section Access – Infinity Insight Blog.

There is some content-overlapping within the above used links but I think the complement to each other is useful and of course you will find many more interesting postings here within the qlik community to these topic - the notes here are a good starting point to go further.

Have fun!

Marcus Sommer

          How To / Missing Manual

Hi All,

The script below loads the data into qlikview file for multiple sheets in a single load by using the Loop statements. 

LET vStartSheetNumber = 1;

LET vEndSheetNumber = 50;

LET vExcelFileName = 'Data';

// Generate Empty table

Data:

LOAD

          '' AS Data.Field1,

  '' AS Data.Field2

AutoGenerate(0);

FOR index = vStartSheetNumber TO vEndSheetNumber

     Concatenate(Data)

     LOAD

          *

     FROM [$(vExcelFileName).xlsx]

     (ooxml, embedded labels, header is 10 lines, table is [Page $(index)]);

NEXT

Note : in my excel file the sheet names are in the patter of Page 1, Page 2, ..... Page n.

Hope this helps others.

Regards,

Jagan.

Me and a colleague have put together a new Calendar, combining a super fast method for generating the needed calendar, while also removing a lot configuration options in the previous methods.

So if you're using a  Min/Max based approach like the one described here: Creating A Master Calendar

Then you're properly used to waiting for the Min/Max to find the largest values in your dataset. This can take minutes. What we have here takes less than a second. I've actually not been able to time this, because this calendar script takes less than a 1 seconds even on billions of rows on decent hardware. The only exception to this is if you use a date range starting before christ and spans a thousand years. If you have a date range, with a lot of holes in it (dates with no data), then you should use the last parameter:

Call Calendar('[Date (NK)]','MyCal','Pre.','','true');

The Attached qvw shows both methods (remember the qvs file).

I've not commented it, because most people will not grasp what is going on anyway, and don't need to

To try it out, include the following in your script, and add this to your code:

Call Calendar('DateField');

/* ************************************************************************************

itelligence Standard Qlik Calender Torben Seebach/Martin Didriksen Special Thanks to Rob Wunderlich and John Witherspoon

Configure Calender parameters

  Syntax:

       Calendar(DateField[,CalendarName][,Prefix[,Suffix[,FullCalendar]]])

  Example:

       Call Calendar('Date (NK)','MyCal','Pre.','','true'); // creates a the MyCal table and fields are prefixed with Pre. In the superfast variat

       Call Calendar('Date (NK)'); //creates a table called calendar based of "Date (NK)" field

  Mandatory:

       @_DateField Contains the name of the field to connect the calendar to

  Optional:

       @_CalendarName Contains the name of the calendar we create      
       @_CalendarPrefix A Prefix for all fields
       @_CalendarSuffix A Suffix for all fields      
       @_FullCalendar If this contains a value then it creates a calendar with all dates from Min(Date) to Max(Date), default is the slowest option

************************************************************************************ */

Sub Calendar (_DateField,_CalendarName,_CalendarPrefix,_CalendarSuffix,_FullCalendar)

  Let _StartTime = Now(); 
  Let _CalendarName = If(Len('$(_CalendarName)')=0,'Calendar','$(_CalendarName)');
  Let _CalendarPrefix = If(Len('$(_CalendarPrefix)')=0,'','$(_CalendarPrefix)');
  Let _CalendarSuffix = If(Len('$(_CalendarSuffix)')=0,'','$(_CalendarSuffix)');
  Let _FullCalendar = If(Len('$(_FullCalendar)')=0,1,0);
  Let _DateField = PurgeChar(_DateField,'"[]');

  "$(_CalendarName)":
   LOAD
        Distinct [$(_DateField)] as [$(_DateField)],
        Text(Date([$(_DateField)])) as [$(_CalendarPrefix)DateText$(_CalendarSuffix)],
        Year([$(_DateField)]) as [$(_CalendarPrefix)Year$(_CalendarSuffix)],
        Week([$(_DateField)])&'-'&Year([$(_DateField)]) as [$(_CalendarPrefix)WeekYear$(_CalendarSuffix)],
        Week([$(_DateField)]) as [$(_CalendarPrefix)Week$(_CalendarSuffix)],
        Month([$(_DateField)]) as [$(_CalendarPrefix)Month$(_CalendarSuffix)],
        Year([$(_DateField)])&'-'&Text(Date([$(_DateField)],'MM')) as [$(_CalendarPrefix)YearMonth$(_CalendarSuffix)],         'Q'&Ceil(Month([$(_DateField)])/3) as [$(_CalendarPrefix)Quarter$(_CalendarSuffix)],
        AutoNumber(MonthStart([$(_DateField)]),'_MonthSerial') as [$(_CalendarPrefix)MonthSerial$(_CalendarSuffix)],
        AutoNumber(QuarterStart([$(_DateField)]),'_QuarterSerial') as [$(_CalendarPrefix)QuarterSerial$(_CalendarSuffix)],

        AutoNumber(weekyear([$(_DateField)]) &'|'&week([$(_DateField)]),'_WeekSerial') as [$(_CalendarPrefix)WeekSerial$(_CalendarSuffix)] ;

  If _FullCalendar=1 Then
        LOAD Date(_DateStart+(Iterno()-1),'$(DateFormat)' ) as [$(_DateField)]
               While (_DateStart+(Iterno()-1)<=_DateStop);
        LOAD
               Floor(Min(Fieldvalue('$(_DateField)',RecNo()))) as _DateStart,
               Floor(Max(Fieldvalue('$(_DateField)',RecNo()))) as _DateStop
        AUTOGENERATE FieldValueCount('$(_DateField)');
Else
        LOAD Num(Fieldvalue('$(_DateField)',RecNo())) as [$(_DateField)]
        AUTOGENERATE FieldValueCount('$(_DateField)');
End If

  /* clean variables  */

Let _TotalTime = Round((Now()-_StartTime)*60*60*24,0.00000000001);
Let _StartTime = NULL;
Let _vDateStart = NULL;
Let _vDateStop = NULL;
Trace $(_CalendarName) created in: $(_TotalTime) seconds;
Let _TotalTime = NULL;

ENDSUB

Updated 2014-05-23:

• Implement preceding load method suggested by Rob Wunderlich
• Fixed Timer to show seconds
• Corrected error in documentation
• Added PurgeChar so [Date A],"Date B" also works.
• Thanks to Stephen Redmond for pointing to a boolean error in line 22

Other than my brilliant Collegue Martin Didriksen, I also want to give John Witherspoon some credit for inspiration on the autogerate method used. I think he was the first who posted it online.

The Most vital Set Analysis that we are using in creating our reports. Why we don't make it simple! This document will make it simple and straight forward to everyone. Good luck

Hi all,

the product JDBC Connector reached end-of-life because it's technically outdated. Support of it ends 2018-12-31.

The successor product is the TIQ Java Service Connector and can be obtained here:

https://www.tiq-solutions.de/en/tiq-java-service-connector/

Best regards,

Ralf

--------------------------------

Hi everyone,

this is a demo version of our JDBC Connector for QlikView. It will enable a lot of non-ODBC/OLEDB sources for loading data into QlikView.

Also, it would give much more performance on QV 9 loads (Oracle, mysql etc.) and could speed up on complex network environments (like VPN, firewalls, WAN).

Please try it out and give us some feedback!

Installation instructions:

• install the JDBC Connector and config the JDBC driver (mostly a .jar file)
• connect with JDBC URL (e.g. jdbc:oracle:thin:@localhost:1521:ex)
• example connect statement for Oracle (will be included by connect popup):
  CUSTOM CONNECT TO "Provider=JDBCConnector_x64.dll;jdbc:oracle:thin:@localhost:1521:ex;XUserId=SbKdMaFNRLOA;XPassword=aWELJaFNRbTA;";

Missing feature (waiting for QV support):

• no select option for database/owner on table wizzard

Thanks to my colleague Sven "Benno" Uhlig who brought up this idea and implementation!

Ralf

UPDATE:

This is a new version which supports DDL and DML statements too. You will get a result set if a DML statement was executed with the update count.

Just try script calls like this:

DATA:

SQL CREATE TABLE test2 (col1 INT)

;

SQL insert into test2 (col1) values (1)

;

SQL update test2 set col1=2

;

SQL drop table test2

;

UPDATE:

New version comes with an installer and config dialogs. See also: http://www.tiq-solutions.de/display/enghome/ENJDBC for a list of possible JDBC sources.

Hi All,

Please find the Fiscal Year - Master Calender script and application.

Set vFM = 4 ;
LET vDateMin=num(makedate(2016,01,01));
LET vDateMax=floor(monthend(today()));
LET vDateToday = num(today());

CalenderTemp:

LOAD Distinct Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY') AS Date,
month(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS Month,
year(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS Year,
monthname(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS Monthname,
Week(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS Week
AutoGenerate 1 While $(vDateMin) + IterNo() - 1 <=$(vDateMax);

Calender:
Load *,Dual(fYear-1 &'/'& fYear, fYear) as FYear,  // Dual fiscal year
   Dual(Month, fMonth)  as FMonth   // Dual fiscal month
  ;
load *,
Year + If(Month>=$(vFM), 1, 0) as fYear,  // Numeric fiscal year
   Mod(Month-$(vFM), 12)+1  as fMonth   // Numeric fiscal month
Resident CalenderTemp;
drop table CalenderTemp;

Attached is an example of stock aging in SAP. In this example I have used the MSEG (Document Segment) and MKPF (Material Document Header) tables to identify both the current stock position and the purchases (the In movements of stock). The final stock value can then be distributed across the most recent stock in movement records to assign a date as to when this stock was received (based on a FIFO - first in first out methodology). The MKPF table is required as there was an incomplete set of records in the BUDAT (stock-in date) field in the MSEG table in this implementation (this may be different at other sites).

The basic process is as follows:

1. identify the purchase instances (headers) from the MKPF table. This is required to get the stock-in date for each purchase instance (BUDAT).
2. Join in the purchase records (where SHKZG = 'S' to identify in movements of stock) and the unit cost of these movements by material (MATNR) and location (LGORT). The MSEG and MKPF tables join based on the MANDT, MBLNR and MJAHR fields. I have chosen to right join the MSEG table as I only require the purchase records (i.e. SHKZG = 'S').
3. Calculate the latest final stock values for each Material (MATNR) and Location (LGORT) and add this against each purchase line. This does repeat the final stock value across many lines but that is used in the next step.
4. Calculate the distribution of the current stock over the most recent preceding purchases. I have also calculated the cost of the stock based on the unit value of the final stock as calculated in step 3.

As this involves a bit of logic to look at preceeding values etc I have attached an example which shows the 4 steps in the coding that you can copy and reuse. The example is based on the QVDs as they would be generated using the SAP Connector script generator** so if you have used this it should be an easy reload of this data.

**Note: the MATNR field is renamed to make it a key in MSEG as [%MATNR_KEY].

Any clarifications or improvements please feel free to add in comments below.

Below script helps in loading all the tables in the database and store that tables in QVDs.

ODBC CONNECT TO MyODBC;

SQLTableList:

LOAD "TABLE_NAME" as  TableNames;

SQL SELECT DISTINCT "TABLE_NAME"

FROM "INFORMATION_SCHEMA".COLUMNS;

Note:

Information schema.columns is for MS SQL Server database, you can replace that with the euqivalent table in the corresponding database. 

For oracle ALL_TABLES   ALL_TABLES

For MYSQL - all_tables or SHOW_TABLES

How to List All Tables and Describe Tables in Oracle, MySQL, DB2 and PostgreSQL | OneWebSQL

MySQL :: MySQL 5.7 Reference Manual :: 13.7.5.37 SHOW TABLES Syntax

Let vTableCount = NoOfRows('SQLTableList');

Let vTableCountQVD = 'dbo.[' & NoOfRows('SQLTableList') & ']';

For i = 0 To $(vTableCount) -1

LET vMyTableName = 'dbo.[' & Peek('TableNames', $(i), 'SQLTableList') & ']';

LET vMyTableNameQVD = Replace(Peek('TableNames', $(i), 'SQLTableList'), ' ', '');

$(vMyTableNameQVD):

SQL SELECT *FROM $(vMyTableName);

STORE $(vMyTableNameQVD) INTO $(ExtractQVD)$(vMyTableNameQVD).QVD;

DROP Table $(vMyTableNameQVD);

Next i

Hope this helps others.

Regards,

Jagan.

Hello Everyone,

I have attached the document for the important Qlikview functions used in script as well as in UI.

Please have a look and also feel free to update the document or comment in the session for the functions which is missed.

Generic Keys is a way to define keys between tables in a more general way so that their values can represent other things than individual key values; they can represent groups of key values or any key value. As an example, you can combine product IDs, product group IDs and a symbol for all products into one key field.

You can use generic keys to solve many data modeling problems:

• Authorization table with OR-logic between fields
  If you have an authorization table you sometimes want to have a slightly more complex access restriction than a simple logical AND between fields. It could be e.g., that a user is allowed to see sales for all regions for a specific product and at the same time the European sales for all products. Generic keys can be used here.
• Mixed dimensional granularity in a single fact table
  Often you want to compare actual numbers with budget numbers. The standard method is to concatenate these two tables into one common fact table. However, this new fact table could have mixed granularity in many of the dimensions. Generic keys can be used here.
• Multiple fact tables linked using a master link table
  Sometimes you have fact tables that are so different that you don’t want to concatenate them. To solve this problem you can make a data model that has a central link table and uses generic keys.

See more in the attached files.

HIC

PS I have been asked to make an example on comparing budget and actual numbers, so here it comes as a second attachment. It is a zip file with a mock-up order database with a budget. Create a new empty qvw; expand the zipped files in the same folder as the qvw; insert one of the two included script files into the qvw and run the script.