Qlikview Actions - Launch another application (eg. Open Excel and file)

Neat little trick in Qlikview.

A Qlikview report reads from an Excel file to get some data.  User asked if could add a link to open that Excel file (so they can check source / update).

Created a Button and added External Action -> Launch.

Application = "Excel".
FileName = "\\.....xlsx".

Was pleasantly surprised that simply putting in Excel was sufficient to identify the application.  Thought it may have needed a full path to an .exe which may have been problematic as Excel.exe might not be in the same place on all users computer.

Qlikview Lookup Function

A quick note on the Lookup Function

From the help screen

lookup( fieldname, matchfieldname, matchfieldvalue [, tablename] )

Returns the value of fieldname corresponding to the first occurrence of the value matchfieldvalue in the field matchfieldname.
Fieldname, matchfieldname and tablename must be given as strings (e.g. quoted literals).
The search order is load order unless the table is the result of complex operations such as joins, in which case the order is not well defined.
Both fieldname and matchfieldname must be fields in the same table, specified by tablename. If tablename is omitted the current table is assumed.
If no match is found, null is returned.

Example:

lookup('Price', 'ProductID', InvoicedProd, 'pricelist')

I occasionally have issues using this function - mainly as I try to avoid it anyway.  Couple of things from trial and error:

• The field names with the ' ' literal wrapper.  DO NOT wrap them with [ ] inside the ' ' even if the field usually needs them.  It will confuse Qlikview and won't find your match.
• As it says, should always use the tablename, as usually not trying to use the current table name.

Qlikview Chart kept flashing and going back to top of scroll - use of Now() function

I have a chart which worked okay, except for it kept flashing and whenever scrolled down to see more values, it jumped back to the top.  Couldn't work out why.  The data model it uses is a bit messy with fields from three different tables getting mixed together, plus set analysis and other calculations.

Did several changes, including to data model, but still couldn't resolve, until checked out something.

The chart lists customers won in last few months.  By default the document has selected month being only the last month, so the chart uses set analysis to extend.  It uses a variable to determine the start month which originally was MonthStart(Min(Date_Month),-3) to get the starting date for set analysis.

Then user wanted instead of last 3 months, back to start of the fin year (or possibly other customised fields).  I setup another variable to calculate how many months were needed to go back to include the start of the year.  For that variable I used Month(Now()) to work out which month we were in and work back from there.

That was the problem.  By including Now() in the variable, Qlikview constantly recalculated that variable - once every second I guess - and the set analysis calculation in the chart.  Hence the flashing and the scrolling issue.  I've replaced Now() with Today() so now will only cause a problem once a day, and only if user is up late.

Probably better methods to get the same result, but important takeaway was using Now() has risks.

Some SAP BW Examples

I work with SAP BW as the datawarehouse feeding our Qlikview reporting.  I've picked up a fair bit of SAP BW and ABAP over time, but struggle to quickly remember things that I have to do oonly occasionally - always have to go back to searching out methods.  This is how I will remember - by including some examples.

   Get CALWEEK from CALDAY or

Using RST_TOBJ_TO_DERIVED_TOBJ function.

 

REPORT Z_MICHAEL.
constants: rs_c_true           TYPE rs_bool      VALUE 'X'.
data: lv_date type d.
data: l_chavl(60)        TYPE c,
      l_chatt(60) type c, "rsd_chavl,
      lv_week type /bi0/oicalweek.

lv_date = sy-datum.

DO 31 times.
  lv_date = lv_date + 1.
  write / lv_date.
  CLEAR l_chavl.
  l_chavl = lv_date.

  CALL FUNCTION 'RST_TOBJ_TO_DERIVED_TOBJ'
    EXPORTING
      I_TIMNM_FROM = '0CALDAY'
      I_TIMNM_TO = '0CALWEEK'
      i_timvl   = l_chavl
      i_fiscvarnt = 'Z6'
      i_buffer           = rs_c_true
    importing
      e_timvl = l_chatt
    EXCEPTIONS
      incompatible_tobjs = 1
      no_input_value     = 2
      fiscvarnt_missing  = 3
      input_not_numeric  = 4
      wrong_date         = 5
      wrong_fiscper      = 6
      x_message          = 7
      OTHERS             = 8.


  write l_chatt.
ENDDO.