Trigger E-mail on button click in qlikview

This scenario is helpful if you want to create "Contact Me" or help kind of email trigger on your app.

You can follow below steps to trigger a mail from qlikview button/test Box.

•  Create a button and set a trigger under Properties - "Open URL".

• Add below as URL 

='mailto:' & 'mailid@email.com' & '?subject=Subjectofmail'& '&body=msg in body, %0A nextline  %0A '

this will create a button with ability to trigger a mail on click.

thanks,

Shiv Shrotriya

  

Setting a List Box in AND-Mode

AND-mode in List Boxes

Multiple selections within a field are by default interpreted as logical OR, which means that data associated to any of the selected field values will be shown in the other fields.

It is however possible to set list boxes to AND-mode instead. When multiple selections are made in a list box set to AND-mode, only data associated to all of the selected field values will be shown in the other fields.

Example:

Your data source contains information about which customers have purchased different articles.

When you select a couple of articles in the default mode, QlikView will show you the customers that have purchased any of the selected articles.

When you select a couple of articles in AND-mode, QlikView will show you the customers that have purchased all of the selected articles.

AND-Mode Prerequisites

The mode for the field is set in List Box Properties: General tab.

When And mode is activated, an ampersand ("&") will be displayed in front of selected values.

If clicking and holding a value for a moment, the selection will switch from (AND) selected (green) to NOT selected (red). The ampersand will also be replaced by an exclamation mark ("!"). The NOT selection which is a forced exclusion of the value or values so marked, can only be made when a list box is in And mode.

AND-Mode criteria

A field cannot always be set to logical AND-mode. The reason for this is that the and alternative is logically meaningful only if the concerned field is linked to only one other field. The following criteria must be fulfilled:

• the field must only exist in one logical table,
• the field must be the second column of no more than two columns,
• the table must not contain any duplicate records, and
• the field must be loaded with the distinct qualifier. If the table is loaded using a select statement, you must use a preceding load distinct *.

Setting a List Box in AND-Mode

The following procedure describes how to set a list box in AND-mode instead of the default mode (logical OR). Perform the following steps:

1. Make sure that the AND-mode criteria are fulfilled.
2. Right-click on the list box and select Properties.
3. On the General tab, mark the And mode check box.

Forced Exclusion

Forced exclusion (sometimes referred to as NOT-selection) is closely related to the AND-selection. The forced exclusion makes it possible to explicitly exclude a field value, which means that the solutions found by QlikView may not be associated with the excluded value.

The forced exclusion is made by clicking a cell and keeping the mouse button pressed until the cell turns red. Ctrl-clicking in this manner is equivalent to an exclusion that is an additional requirement to the previous selections/ exclusions. The forced exclusion can only be made on a field that is in AND-mode.

Mapping load to load multiple tables in one Go.

TABLES:
MAPPING LOAD * INLINE [
ID,TABLE
1,Table1
2,Table2
3,Table3
4,Table4
5,Table5
];




FOR A = 1 TO 5

let vTable = ApplyMap('TABLES',$(A));
'$(vTable)':
LOAD *;
SQL SELECT * FROM $(vTable);



Store $(vTable) into into $(vTable).QVD;

DROP TABLE $(vTable);

NEXT

Removing Preceding Zero from any filed in QLikview

Requirement is to remove leading zeros from field values of Numnber. This is a text field and can contain numeric or alpha-numeric values.

The trick I could do is:

=replace(ltrim(replace(Field,'0',' ')),' ','0')

Create custom short in QLikview?

Sometimes, we face scenario wherein we need to customize the sort in some specific order of values in Chart\tables.

By default we have option in qlikview to sort data in acceding or descending order.

In expression of Sort tab, use below

Match(Dim,'val1','val2','val3','val4')

or 

Minstring({1}WildMatch(DIM,,'val1','val2','val3','val4'))

Another scenario

how the sort order in a chart can be controlled by a user option.

• Create a input box and set user input in variable lets say -  Vprimecustomer
• in chart sort tab, check expression and use below formula

                       replace(match(Dim,'$(Vprimecustomer)'),0,1000)

this will show user input as first value in chart/table.

thanks,

Calculate MOM calculation in chart or table in Qlikview.

If we need to calculate YOY or MOM calculation in Qlikview. We can achieve it by following below steps.

• If we need MOM revenue, Create a variable for previous month as below.

vPriorMonthYear = Date(addmonths(today(), -1), 'MMM-YYYY')

• This will give you month-year value for Previous month.
• In your chart or table expression, please use below formula.

=Sum(num#([Revenue]))- Sum({<[Month Year]={'$(vPriorMonthYear)'},FISCYEAR=,Month=>}Num#([Revenue]))

This will show the MOM value, means current month revenue minus previous month revenue.

thanks,

Shiv Shrotriya 

Create Hyperlink in qlikview table, chart.

• Go to expression.

• exp. definition: -  filed& '<url>' & field
• Set Representation as "Link".