• What techniques are there to match a number with an interval?
• How does the IntervalMatch prefix work?
• Should I join the interval match table with the event table?
• How do I generate an interval from a single change date?
• What is a Slowly Changing Dimension?
• How do I model a multi-level Slowly Changing Dimension?

These questions and others are answered in this Technical Brief.

Thank you Barry for good discussions.

See also the following blog posts

IntervalMatch

Creating a Date Interval from a Single Date

Creating Reference Dates for Intervals

Slowly Changing Dimensions

HIC

This tutorial presents a script pattern for assigning time dimensions to multiple fact dates in a data model. It answers the commonly asked Forum question "how do I link to two dates"?

The pattern will demonstrate how to link all fact dates to a common calendar as well as using separate calendars for each fact date.

QlikView has an intelligent algorithm to recognize dates independently of which region you are in. In most cases, you will have no problems loading them. It just works and you do not need to think about it. However, in some cases dates are not properly recognized and then you need to add some code in the script to make it work.

HIC

Hello

One reason why you want to use dollar sign expansion is the possibility to store your expressions in variables and then use these variables in your chart expressions. THis will concentrate all business logic in one place and simplify chart expressions. But suppose you want to pass some parameters into these expressions. QlikView supports this, but there is very annoying problem. If there are any commas (,) in your parameter value, QlikView treats this as parameter separator but not as part of parameter value.

Recently I found some workaround for that. Suppose we have and expression stored in a variable varExpr. The value o expression is SUM({$< $1 >} quantity).

Now if I create a chart expression =$(varExpr(year)), this will convert into =SUM({$< year>} quantity) and it will calculate sum ofd quantity field with year field cleared. But what if I want to clear not just year field but month as well. I cannot do this by setting my chart expression to =$(varExpr(year, month)) this will not work and as a result I get the same expression =SUM({$< year>} quantity).

But If I use SUM({$< $(=REPLACE('$1', ';', ',')) >} quantity) for variable value and then =$(varExpr(year; month)) for chart expression - this will give me the expression I want =SUM({$<year, month>} quantity).

Cheers

Darius.

Power Tools for QlikView

Version: 1.3.2

..again! A small update of one tool, and test of another.

Download here

https://www.dropbox.com/sh/8hac72aljgqlr7d/AAASkzbM2H1OoVcLE_wa0kWGa?dl=0

For the absolute latest version, please see Power Tools for QlikView - One-stop-shop

QlikView 12.X support matrix

Reviewed/updated with support for QlikView 12

• QlikView Server Agent *tested* - 1.6.2 - supported
• QlikView Server Super Agent *updated*- 1.7.3 - supported

Release notes here: Power Tools 1.3.2 for QlikView - Release Notes

Readme here: Power Tools 1.3.2 for QlikView - Readme

Want to collaborate?

Do you have questions, thoughts, suggestions or bugs to report on Power Tools? Please post in the forums on QlikCommunity: https://community.qlik.com/community/qlikview/management

Also check out our (currently dead, soon to be revived) Twitter: @QvPowerTools

DISCLAIMER

Power Tools are a collection of software programs and tools used for troubleshooting purposes only. Power Tools are provided free of charge and are not supported. Power Tools are not official QlikView products and are provided without warranty. Use of Power Tools is entirely at the user's own risk.

What is Control Chart?

The control chart is a statistical analysis used to study how a process changes over time. Data are plotted in time order. Control charts are used to routinely monitor quality or simply you can say, control chart is process improvement technique.

What is a process?

A process is simply what we do. It can be filling out an expense report, checking a person into a hospital, burning a CD with music, driving to work, filling a prescription, etc. All these process generates the data. We take these generated data and plot on control chart to improve efficiency.

There are many types of control chart, but we are going to use the basic control chart with Std dev method to understand the concept.

Followings are some scenario where we can use the control chart

1) In manufacturing to test if machinery are producing products within specifications.

2) suppose you want to reduce the time it takes to admit someone to the hospital.

3) If you want to know how views are going up or down for particular website.

Components of Control Chat

1) Avg line

2) UCL (Upper Control line) = Avg Value + Std Deviation* Std Dev Multiplier

3) LCL (Lower control line) = Avg Value - Std Deviation* Std Dev Multiplier

So, basically we can see which are the points "Out of Control" or "In Control".

You can control your Std Dev multiplier using variable.

How to create the Control Chart.

1) Create the Combo Chart.

2) Take any Time dimension of your interest which is suited for your analysis, here I have taken the MonthYear. Also    make it Continuous in Axis setting

3) Create two Expression, one for Line and second for Symbol.

4) Create expressions for Avg line, UCL & LCL which will be used as reference lines in the chart. Check the Chart properties ->Presentation for reference line setting.

5) Create the Background color setting for Symbol expression

if( sum(Sales) < vUCL and sum(Sales) > vLCL, LightGreen(), LightRed())

6) Finally the chart will look like below

There are some other useful links available to create the control charts

QlikView Control (SPC) Chart

See also :

Benford's law

Bollinger Bands

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.

When we want to do something with QlikView, we face so many possibilities to achive the goal. And sometimes, the first idea we have is not necessarily the simplest one.

I wanted to do create YTD and moving totals measures, difference (absolute and in percentage) of these moving totals versus the Year ago. QlikView is a BI tool: no problem I thought. Well, it was not so simple ...   The expressions became so complex that I finally did not understand them any more!

After some searches and many tries,the solution to such a chart is very simple. You just need to look at the right place.

As you can see, this chart contains actual data and the one of the year ago, a YTD starting in January and the same for the Year Ago, Rolling 3 and 12 periods and the same for the Year Ago. These values are contained in a TIME ANALYSIS axis that the user can select in a simple List Box to display only YTD and Rolling12 for example:

The document will help you to understand the modification you have to do to your data model to make this report very simple. As you can already see, the expression is the simplest expression in QlikView: sum(VALUE).

This simplicity let you afterwards mix the different measures of the database with this TIME ANALYSIS axis : get the current sales values, but the share the products represents the last 12 motnhs, and a difference vs year ago:

Many thanks to G. Wassenaar and his post that helped me to understand where to look exactly :  http://community.qlik.com/docs/DOC-4252

Have a nice reading

Fabrice AUNEZ

I have attached sample QVW and Sample data. Hope this will be helpful for all..

Have you ever wanted to create a Gantt chart in QlikView, only to find out that this chart type is not one of the pre-defined charts? Then you should be happy to learn that it is possible to create a Gantt chart and that it is not very difficult. See more on Recipe for Gantt chart.

HIC

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

Year, Quarter, Month and Week To Date are the common analysis that I seen many applications. I will share the expression to do here

First to do this your data model should have the DateField in number format by applying floor

Similar to this

Floor(DateField) AS DateNum //it will gives you one whole number to represent date

YTD - Year To Date

A date should be selected and it will look for the Starting date of the year to the selected date.

Ex: date selected is 21-03-2014 then YTD is 01-01-2014 to 21-03-2014

Expression would be

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

QTD- Quarter to Date

In the place of year use Quarter

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(QuarterStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

MTD- Month to Date

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

WTD- Week to Date

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(WeekStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

if you want you can set a variable value as 'Year', 'Month', 'Quarter', 'Week', lets say vToDate and go with single chart and single expression

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num($(=vToDate& 'Start(Max(DateNum))')))<=$(=Max(DateNum))"}>} Sales)

Will keep your expression simple

Regards,

Celambarasan

Searching in field values is a powerful feature in QlikView (all this should also work in QlikSense, but I haven't tested everything). You can use searches for example in set analysis field modifier, select in field actions and not to forget filtering e.g. list boxes in the frontend.

Hence knowing the power of searches is vital to developers as well as end users.

Unfortunately (in terms of 'getting started with search'), there are several possible search modes, have a look at The Search String‌ to get an overview.

Text searches continues explaining. Some of the search modes are explained a bit more detailed: The Expression Search

And there are some things to consider we would hardly know without following Henric Cronström's ( hic‌ ) great design blog: Search - But what shall you find?‌

Not to forget, there is a nice cheat sheet: Qlik Sense Search Cheat Sheet,‌ covering some features, but not everything.

There is also of course some documentation in the HELP file as well as the reference manual, but not what I would call a comprehensive documentation of this essential product feature. Especially, there is few documentation about the so called 'compound search'.

So let us shed some light on

QlikView's Compound Search feature

First, let's create some sample data:

SAMPLE:
LOAD RecNo() as Number, Text
INLINE [
Text
Harry
Sally
Harry & Sally
New Hampshire
New York
Something new
UPPER
not upper
Al Bundy
Airport Newark
me
];

• Triggering a compound search
  A compound search is triggered by enclosing the search filter in parentheses (...)
  The search itself will be performed over the values in the field's symbol table.

• The most simple possible search filter is just inputting a value. In contrary to other search modes, the compound search will search for values that exactely match your search term.
  [Note: A case insensitive comparison will be performed, but according to some comments by Henric in above blog posts, this is considered a bug and may be fixed sometime in the future ]
  
  For example, (1) put into a Number field list box search bar will return only the exact match '1', not all values that contain '1' or start with '1'. Another example: Try upper compaired to (upper) in Text list box search bar.

• Wildcards
  You can use wildcards in a compound search, too. A question mark '?' is a placeholder for any single character, a star symbol '*' for any number of not defined characters.
  (?e) will return any value with two characters, second being an 'e'. (*e) will return any value that ends with an 'e'.

• Quoting
  If your values contain spaces, you need to quote the value: ("Something New") will find the value, (Something New) not.

• Wildcard ^: Beginning of word
  If you quote your search string, you can also use the wildcard '^' to match your search term with beginning of words in your values
  (read further down why you need to quote here, even when there is no space contained):

[Note: As Henric commented to one of his blog posts, there is no escape character to escape a wildcard, hence if you want to search for e.g. '?', you would need another search mode, e.g. advanced search using =index(FIELD,'?') ]

• Combining searches using search operators:
  Now it's getting a little bit more advanced: If you want to create some more complex searches, you can combine two or more searches with search operators:
  • OR operator '|'
    Using this operator, your compound search will return values that match either the first or the second search, e.g.
    
    
    

• AND operator '&'
  Using this operator, your compound search will return values that match both first and second search, obviously, this does only make sense if you are using wildcards:

• Up to this point, all this can be found here in the forum, in Henric's design blog referenced above, in the comments to his blog, in the tech doc or in some other threads. But I hope this document will add something useful to all this already available, though distributed material. Introducing the AFAIK not really well-known
  XOR operator '^'
  Using this operator, your compound search will return values that match either the first or the second search, but not both:

Another example for the XOR operator would be to find the complement of a search (for example search for *Sally*):

You could achieve this by selecting (Sally), then use the context menu to select excluded, but you can also use a single search: (* ^ *Sally*)

[Agreed, the syntax would be easier if there was an operator for NOT or complement, but I haven't found it so far. If you know it, please drop a comment below.]

All these operators can be used to not only combine two, but many searches: (me|Harry|Sally)

This would be a good point to end this document. But there is something more I haven't read about so far:

• Nesting compound searches
  You can nest a compound search into another compound search. Why you may want to do this?
  
  For example if you want to create a search like 'Return all values containing 'Harry', but exclude values where 'Sally' appears.
  Since to my limited knowledge, there is no 'NOT' or complement unary search operator, you can emulate this search using the existing three search operators described above:

You can achieve similar without nesting, but take care of the order of your searches and operators:

Some (or all?) of these results can of course be achieved using other search modes, I just wanted to focus on the compound search mode in this blog post. I hope all mysteries of the compound search have been revealed.

If you have any comments or questions, drop a comment below.

Have fun!

edits:
2016-02-02: Complement can be created easier, just using (* ^ *Sally*), added some more examples, reorganized some parts

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‌

Over a year and half ago, I published instructions for creating a class to communicate with Qlikview api using the Python language. Here is the link for your convenience: QLIKVIEW AUTOMATION WITH PYTHON

Many of you have written and sent me messages requesting to make a new post containing the instructions to extract Qlikview pivot tables in excel and send them via email.

So after more than a year and a pandemic in action, here is an example of extracting Qlikview tables and sending as an email attachment. All tested with Python 3.9

You can found the full files and code at  GITHUB 

Here I post only the Python script.

Basically, there is a configuration file of the email parameters and the path of the qlikview file. The qlikview script defines the table  containing the people who must receive the emails with their addresses and the table fields to be filtered with the filter value to be used to reduce the data.

Hoping you like it,  for question or issues write me on GitHub.

Matteo Minischetti

PyQlikSender.py

from win32com.client import Dispatch  # pywin32 module
import smtplib
from email.message import EmailMessage
import os.path
from datetime import datetime
import configparser
import logging

current_path = os.getcwd()
config = configparser.ConfigParser()
config.read_file(open(r'PyQlikSenderConf.txt'))
QLIKVIEW_DOCUMENT = config.get('configuration','QLIKVIEW_DOCUMENT')
SMTP_ADDRESS = config.get('configuration','SMTP_ADDRESS')
SMTP_PORT = config.get('configuration','SMTP_PORT')
EMAIL_FROM = config.get('configuration', 'EMAIL_FROM')
EMAIL_LOGIN = config.get('configuration', 'EMAIL_LOGIN')
EMAIL_PASSWORD = config.get('configuration', 'EMAIL_PASSWORD')
LOGFILE = config.get('configuration', 'logfile')
QVRELOAD = config.get('configuration', 'QVRELOAD')


class QlikView:
    def __init__(self😞
        self.app = Dispatch('QlikTech.QlikView')

    def opendoc(self, docname, username, password):
        doc = self.app.OpenDoc(docname, username, password)
        return doc

    def reload(self,doc):
        doc.Reload()

    def clearall(self,doc):
        doc.ClearAll()

    def apply_field_filter(self, doc, filter_field, filter_value):
        doc.Fields(filter_field).Select(filter_value)

    def closedoc(self, doc):
        doc.CloseDoc()



def sendmail(email, subject,  name, surname, filename):
    msg = EmailMessage()
    msg['Subject'] = subject
    msg['From'] = EMAIL_FROM
    msg['To'] = email
    content = 'Dear ' + name + ' ' + surname + '  your report is attached here'
    msg.set_content(content)
    try:
        with open(filename, 'rb') as xls:
            msg.add_attachment(xls.read(), maintype='application', subtype='octet-stream', filename=xls.name)
    except Exception as e:
        print('cannot open attachment to send, error :' + str(e))
    try:
        with smtplib.SMTP_SSL(SMTP_ADDRESS, SMTP_PORT) as smtp:
            smtp.login(EMAIL_LOGIN, EMAIL_PASSWORD)
            smtp.send_message(msg)
    except Exception as e:
        logging.info('cannot send email to : ' + email + ' , error :' + str(e))


def manage_document(docname, current_path, tb_email):
    username = None
    password = None
    q = QlikView()
    version = q.app.QvVersion()
    print(version)
    doc = q.opendoc(docname, username, password)
    if QVRELOAD == 'Y':
        q.reload(docname)
    email_table = doc.GetSheetObject(tb_email)  # the object table containing email to send and filter to apply
    today_date = str(datetime.today().date()).replace('-', '_')
    rowiter = 0
    rows = email_table.GetRowCount()

    while rowiter < rows:
        title = email_table.GetCell(rowiter, 0).Text
        name = email_table.GetCell(rowiter, 1).Text
        surname = email_table.GetCell(rowiter, 2).Text
        email = email_table.GetCell(rowiter, 3).Text
        company = email_table.GetCell(rowiter, 4).Text
        filter_field = email_table.GetCell(rowiter, 5).Text
        filter_value = email_table.GetCell(rowiter, 6).Text
        tb1 = email_table.GetCell(rowiter, 7).Text
        print(title, name, surname, email, company)
        rowiter = rowiter + 1
        subject = "Qlikview report service for: " + company

        if rowiter > 1:
            filename = tb1 + '_' + name + '_' + surname + '_' + today_date + '.xls'
            q.clearall(docname)  # clear all filter
            doc.Fields(filter_field).Select(filter_value)  # apply filter
            try:
                chart = doc.GetSheetObject(tb1)
            except Exception as e:
                logging.info('error: ' + str(e))
            chart_path = current_path + '/' + filename
            chart.ExportBiff(chart_path)
            sendmail(email, subject, name, surname, filename)

    q.closedoc(doc)
    q.app.Quit()


if __name__ == '__main__':
    doc = current_path + '/' + QLIKVIEW_DOCUMENT
    logging.basicConfig(format='%(asctime)s - %(message)s', filename=LOGFILE, encoding='utf-8',
                        level=logging.DEBUG)
    tb_email = 'email'   # qlikview table containing email address and info
    manage_document(doc, current_path, tb_email)

For those that find this document in a search - you may also want to check out these videos to learn Set Analysis:

A Beginners' Introduction to Set Analysis

About the attached document:

The doc is organized by question:

- to select all or just known members

- to select through search strings, variables that can store just members but also the whole set

- to select using two fields, a boolean test, a function like concat(), sum(), p() or e(), rank()

In this updated version, I have added some few sections and some examples.

It is a translation of a french doc I have written few weeks ago: http://community.qlik.com/docs/DOC-4889

About
What is it? What does it do?

This is a tool that I use to quick check shared file content and do some usual maintenance job on it. After playing with colleagues for a while, I think it'd be nice to share with the community and get some feedback about if/how I should proceed with this personal project.

This tool is a very simple one, it just can open both legacy ".Shared" and new ".TShared" formats of QlikView Shared File, show helpful info in it, and provide some very basic operations on shared objects (currently I have only add support for Bookmark because it's the most commonly used one day-today)

Why another Shared File Viewer?

There has been a Shared File Viewer already for quite a while (in PowerTools package)

The limitation of the existing one is it can't open the new "TShared" format that was introduced lately into QlikView. So if one wants to view new format, they have to convert "Tshared" to "Shared" first and convert it back afterwards, which is really annoying especially the shared file is *big*.

Another limitation for the current one is it provides small subset info of Shared file content and doesn't embed much shared file functions (cleaning, filtering) in it because its development toolchain is out of dated.

Lastly, I found it's not easy to run a Shared File Cleaner without GUI and want something more intuitive.

In short the legacy shared file viewer is inconvenient to use(to me at least 😅 ), especially when it comes to new "TShared" format.

So i think why not just write another tool myself to meet my need - here it comes.

Release Note
Current Stable Release: 0.2

You can find it in the attachment, where the zip file simply contains an exe file that you can run on Windows.

Features:

• Shared objects meta info overview
• Shared Bookmark
  • basic info overview
  • sort by Id, Name, LastModified, LastRecalled and Size
  • Export
  • Delete
• Convert "TShared" to "Shared" and backward.

Hopefully you have time to download and play with it, and, most importantly, give me some feedback about how you think of it, and what other functions you want to include in it in future.

NOTE:

this tool is currently under preview only. and please be CAUTIOUS if you use it with production Shared files. I know the shared content is critically important, so make sure you have backup before touching any Shared Files.