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".

Remove/Add the scroll bar from a chart?

In Qlikview, we have capability  to add or remove the scroll bar in Chart as par the Dimension limit.

By default QV does not include scroll bar even if we have many dimension values, QV just ignore few dimension to fit all the data in chart.
We can include the scroll bar in chart to show fix number of dimension at a time.

• Go to chart Property -> presentation tab.
• Check/uncheck scrolling option for dimension limit.

creating DB connection (oracle) via Qlikview.

Install Oracle driver then.

Make below entry in "tnsname.ora" file. (C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN)

in order to create ODBC/OLEDB credential Via Qlikview.

"DATA SOURCE"=(description=(address=(protocol=tcp)(host= 'hostname')(port=1521))(connect_data=(sid=NLTDEV1)))

Refresh NPrinting report only Dashboard is refreshed in last X hours/minutes.

Some time, we face the requirement to run the Nprinting report only when QMC reload is in success and we have latest data in Report.
We can follow below steps to check whether QV document is refreshed in last n hours.

• Create a variable in your Dashboard similar to below. Here N is number of  hours  to check - How many hours back dashboard was refreshed.

           Let V_RefreshTime = Timestamp(now() + N/24)  ,

•     Go to Nprinting Report, Create a condition rule as below.

• Select the option calculate for your rule.

Save the Nprinting report and task.

Now you have Nprinting report having logic to send the report only when dashboard is refreshed in last N hours. We Can manipulate the logic by just changing the value of N.

cheers,

Shiv

How to populate date missing value and relative data in Qlikview

How to populate date missing value and relative data

Sometimes when you load data into QlikView you find you are missing some value of field but these values are required to populate the chart. Also you need some dummy data(avg figure) for missing values.

It could be like in the table to the right where you have three dates, each with some kind of conversion rate. The table only contains the dates where the conversion rate changed, not the dates between the changes.

However, the user will want to ask the question: “What was the status on this specific day?” In other words, the user wants to be able to click on a reference date to see the number that is associated with this date – but the date might not exist in the source data.

In such a situation, you need to generate the missing dates between the changes as individual records and use the “Rate” value from the previous date.

There are several ways to do this in QlikView, and all of them involve some script programming, using temporary tables. One algorithm is

1. Load the source table containing the rates (below called “Rates”).
2. Find largest and smallest date in the “Rates” table.
3. Generate all dates between the largest and smallest dates (below called “Dates”)..
4. Join the “Dates” table (outer join) onto the “Rates” table.
5. Sort the resulting table according to date.
6. Propagate the value of “Rate” downwards to all records that have NULL in the “Rate” field, using the Peek() function.

Visually, the join and peek steps of the algorithm look like this:

In the QlikView script, the algorithm would look like the following:

     TempTable_Rates:      Load Date, Rate From Rates ;

     MinMaxDate:     Load Min(Date) as MinDate, Max(Date) as MaxDate resident TempTable_Rates;     Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;     Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate')    ;

     Join (TempTable_Rates)

     Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;

     Rates:

     NoConcatenate Load Date, 
          If( IsNull( Rate ), Peek( Rate ), Rate ) as Rate          Resident TempTable_Rates          Order By Date ; /* so that above values can be propagated downwards */

     Drop Table MinMaxDate, TempTable_Rates;

QMC reload job failure Alert mail

• Goto -> QMC-> System -> Setup 
• Select Qlikview Server
• On Right side -> Alerts Tab
• Pass the Email address, in address bar for alert mail.

This will send an alert mail on reload failure on QV document.

Please drop your comments to further discuss this in detail.

Generate your own calendar table in Qlikview?

• We need start date of calendar, in my case I have taken minimum date from fact table.
• We need End Date of calendar, I have taken today's date.

To get min. date from Fact:-

Min:
LOAD min (Date_field) as Min_Date
resident Fact;

let vMin_Date=num(floor(Peek('Min_Date')));
let vMax_Date=num(floor(today()));

To Generate Calendar between min and max date:-

Calendar:
load date(RowNo()+ $(vMin_Date)-1) as Calendar_Date
AutoGenerate ($(vMax_Date)-$(vMin_Date)) ;


This will populate all the dates between min date to max date. Now we can create other date Matrix using Calendar_Date.

Calendar_Table:
load *,
        Month(Calendar_Date) as Month,
  Hour(Calendar_Date) as Hour_Calendar,
  Day(Calendar_Date) as Day,
  Date(floor(Calendar_Date)) as Date,
  Date((floor(Calendar_Date)), 'DD/MM/YYYY') As DateField,
WeekDay(floor(Calendar_Date)) as WeekDay,
Year(date(floor(Calendar_Date))) as Year,
  dual(Month(Calendar_Date)&'-'&Year(Calendar_Date),Year(Calendar_Date)&num(month(Calendar_Date),00)) as YearMonthDis,
  (date(WeekStart(date(Calendar_Date,'DD/MM/YYYY')),'DD/MM') & '-' & date(WeekEnd(date(Calendar_Date,'DD/MM/YYYY')),'DD/MM')) as WeekPeriod,
  MonthStart(floor(Calendar_Date)) as MonthStart,
  Year(Calendar_Date)&num(Month(Calendar_Date),00) as YearMonth,
  Year(Calendar_Date)&num(Week(Calendar_Date),00) as YearWeek,
  'Q'&(FLOOR(NUM(MONTH(Calendar_Date)-1)/3)+1) AS Quarter,
     Date(monthstart(Calendar_Date), 'MMM-YYYY') AS MonthYear,
     Week(Calendar_Date)&'-'&Year(Calendar_Date) AS WeekYear
 
   Resident Calendar;
Drop table Calendar;

Now we have Calendar_Table ready in model to be used.

Date time stamp handling with incremental load using Oracle DB.

• Take Max date from existing QVD file.

maxDate:
load
max(date) as maxDate
FROM
..\1_QVD\your-qvd-file.qvd (qvd);

let vSERVICE_MONITORINGMaxDate = date(peek('maxDate',0,'maxDate'),'DD-MM-YY hh:mm:ss'); // max date parameter

Final_Table:
SQL SELECT *
FROM DB_Table
where to_date(to_char(REQUEST_TIMESTAMP,'DD-MM-RR HH24:MI:SS')) >to_date('$(vSERVICE_MONITORINGMaxDate)','DD-MM-RR HH24:MI:SS');
Concatenate
LOAD *
FROM
[..\1_QVD\your-qvd-file.qvd] (qvd);
store Final_Table into ..\\1_QVD\your-qvd-file..qvd;
drop table Final_Table;
drop table maxDate;


We can also check whether QVD file is empty or not with below Code.

File:
LOAD
QvdNoOfRecords('..\\1_QVD\our-qvd-file.qvd') AS FileNumberOfRecords
autogenerate 1;

let vFileNumberOfRecords = peek('FileNumberOfRecords');


//If file doesn't exist run a full load else run incremental
if '$(vFileNumberOfRecords)' = '' then

//full load
Final_Table:
SQL SELECT *
FROM DB_Table;
store Final_Table into ..\\1_QVD\your-qvd-file.qvd;
drop table Final_Table;
ENFIF