Qlikview Question-4

Difference between Optimized and Un-optimized QVDs:

Optimized load is much faster and preferable especially for large set of data. It is possible if no transformation are made at the time of load and no filtering is done.

The only acceptable filter is using function Exists:

Where Exists (field)

So for example the load can't be optimized:
Load * from .. Where Country = 'US'

But this load can be optimized:
Load * from .. where exists (Country)

Applying any transformations and where conditions and more than one parameter on Qvd is called Un-optimized..

Qlikview Question-3

What is partial loading??

Partial Reload helps to add / replace data to QlikView Applications without reloading the complete application.

Usually When We do a full reload in QlikView application, QlikView drops all the tables from Memory and recreates the Table again from the Script But with Partial reload, we can reload only certain load/Select statement by Keeping other tables in QlikView Memory.

There are two Key Words by which partial reload can be activated in QlikView Scripting.

Add        - Append Data to Existing Table or Add New Table to your Data Model
Replace - Replace Existing Table.

Alternate to Generic load in Qlikview

I have requirement where one of my field values need to be converted to fields and their status should appear as their values

I used generic load to get my requirement but in the process i got the result without generic load 

I have field named Process where it has around 40 values and theirs statuses are in different field named as Status

It looks as below and i have primary field named as ID

Instead of Generic load i used below code to get the expected result 

InitialTable:

LOAD

    "ID",

    Process1,

    Status1

resident Processes;

FinalTable:

LOAD ID

Resident InitialTable;

FOR i = 0 to FieldValueCount('Process1')-1

LET vField = FieldValue('Process1', $(i));

If '$(vField)' <>'' then 

Left Join (FinalTable)

LOAD ID,

Status1 as [$(vField)]

Resident InitialTable

Where  Process1 = '$(vField)';

end if;

NEXT i

DROP Table InitialTable;

I got the result as below after execution 

  

New Features In Gmail 2018

Google is changing how Gmail looks and works.They launched the new Gmail back in April, but until now it’s been optional.Everyone will be switched over 12 weeks after the transition starts.

If you’re seeing the new Gmail for the first time you might be a little overwhelmed. To help you feel a little more at home, here’s a quick roundup of the new features and how to use them.

Hover Actions Let You Act On Messages More Quickly

You can archive emails, delete them, mark them as unread, or even snooze them. It’s a small feature, sure, but it makes interacting with emails a lot faster.

Snooze Emails So You Can Deal With Them Later

Gmail users can now snooze emails without any browser extensions. This removes emails from your inbox only to bring them back later, which is perfect for anything you need to act on eventually.

The functionality is simple: just click the Snooze hover function and choose when you’d like to see the email again. When the times up, the message shows back up in your Inbox.

his is a must-have feature for anyone who likes to keep their inbox clean, so check it out.

Suggests Replies and Can Autocomplete Sentences

“I hope you’re well,” “Looking forward to hearing from you,” “I hope you have a great day.” Emails tend to include a lot of sentences like this, which you’ve probably typed out hundreds of times over the years.

Confidential Mode Causes Emails to Self-Destruct

Email isn’t secure, but this feature can help. Confidential Mode causes emails to self-destruct, meaning whoever you send it to can only read it for a certain period of time. Your message is essentially stored on a Google server instead of being sent via email, allowing you to control who can see the message and when. This isn’t foolproof—the person you send it to could screenshot the email, for example—but it’s a nice little feature that can help keep your information private.

Questions on SAP connector clients 7.0

What are the SAP connector clients in Qliksense??

SAP Bex Connector
SAP SQL Connector
SAP Extractor Connector
SAP Info provider ConnectorSAP BAPI Connector


What are the objects supported by SAP Bex connector 7.0??


Bex Query
Composite Provider

What are the data types can not be downloaded using by the SAP SQL Connector??

A field in an SAP database table is assigned to a Data Type. Fields assigned to the following Data Types cannot be downloaded by the SQL Connector:

• LCHR (Long character string, requires preceding INT2 field)
• LRAW (Long byte string, requires preceding INT2 field)
• RAWSTRING (Byte String of Variable Length)

What are the Objects supported by SAP SQL connector 7.0??

DSO
Info Objects
Info Cube
ADSO

What is Delta load and which connector supports it??

Delta load is used when there are new records added. Instead of extracting all the records, we can extract records which are updated recently. Extractor connector supports Delta load 

What are the objects supported by SAP extractor connector??

DSO
Info Objects
Infocube
Hierarchy 

What are the info providers supported by SAP Info provider connector??

InfoCube
MultiProviderADSO
Composite ProviderOpen ODS view 


What is BAPI connector??

The BAPI Connector enables calls to Function Modules or BAPIs (Business Application Programming Interface) in an SAP BASIS System 6.40, 7.00, and later. The QA method of a BAPI is implemented as a Function module.

Qlikview Question -2

Binary Load

            Loading one .qvw file as a source into another .qvw file is called binary load.

• Only one binary load will be allowed within a .qvw file.
• It should be loaded as first line of the script before the set variables in first tab.
• Using binary we will get the data model from the another qvw file but not the front end object. Please find data model view in the below screen shot after binary load

Below is the sample code:- 

Binary  C:\users\.....\.........\binary.qvw;

How to convert .PGO to XML files in qlikview

As user license information stored in .PGO files in Qlikview, we can not see the information.We can convert them into XML files to get loaded into qlikview. We will see the process of converting PGO files to XML below.

(PGO files are stored in Root folder. you can find the root folder path under QMC > System > Setup > QlikView Servers > QVS@ > Folders tab > Root Folder field)

We can convert by changing parameter in Settings.ini file. Please open settings.ini file and change the parameter to PgoAsXmlAlso=1

Please find in below screen shot

Once the parameter is changed then save the file and restart the Qlikview server service. Now XML files are available in Root folder

Qlikview Question-1

Is there any function which creates Synthetic key in Qlikview?
Yes/No. Please comment if there is any function

Synthetic key is due to more than one common key between tables in Script. We don't have synthetic keys until there are no two common keys between tables. 
But when we use interval match function, It will create synthetic key as this is the nature of this function. Using proper join, we can remove this 

Dynamic field change in Straight table - Qliksense

Below is the process to change the dimension dynamically in table in which all measures remains same.

Build a inline table with one field and give the field names as values for that field

Example inline table looks as below

Load * Inline
[
Dimension
Dimension1,
Dimension2,
Dimension3,
];


Use below expression in the dimension field for straight table

=IF(GetSelectedCount(Dimension)=1,
IF(Dimension='Dimension1',Dimension1,
IF(Dimension='Dimension2',Dimension2,
IF(Dimension='Dimension3',Dimension3,
))),Dimension1)

Use below expression in the straight table label to reflect label name dynamically whenever make a selection in the filter

=IF(GetSelectedCount(Dimension)=1,
IF(Dimension='Dimension1','Dimension1',
IF(Dimension='Dimension2','Dimension2',
IF(Dimension='Dimension3','Dimension3',
))),'Dimension1')

and also use the same above expression in the filter title to get title updated dynamically updated when selection is changed. Default section is 'Dimension1' in all the expressions



Below screen shot explains a filter where the dimension needs to be selected and will change automatically in the straight table

ODAG - On demand app generation in qliksense

–Enables users to generate Qlik Sense applications on-demand i.e. after interactively selecting the data they are interested in discovering insights on.

–Provides an ability to load and analyse data sources with large volumes of data in Qlik sense.
–Loading entire volume of data from a larger data source is inefficient 
–But to make representative analysis the entirety of the data should be accessible
On demand provides a solution for that provides users with a "shopping list" experience enables them to interactively populate their apps with a subset of data such as time period, customer segment, or geography.

                  ODAG – Configuration
On-demand app generation is controlled by the On-demand app service which is managed in the Qlik Management Console.
–The service is disabled by default and must be enabled before selection and template apps can be linked and on-demand apps generated.

                    ODAG – Components

–On Demand Selection App: Loads an aggregate view of the big data source with modest dimension granularity

–On Demand Template App: its load script contains data binding expressions used to formulate the queries made on the data sources.

–App Navigation links

–Navigation bar – Each sheet in a selection app contains an app navigation bar

–Navigation Point – Once a navigation link is dropped in an app’s sheet’s navigation bar it’s called a navigation point

 

New visualizations and features in Qliksense April 2018

                                             Waterfall Chart

Shows how the Initial Value is affected by Intermediate Positive and Negative Values

Whole Bars represents Initial and Final Values of Chart.

Floating/Hanging Bars represents Intermediate Positive and Negative Values

Example below shows how Initial Value Sales is impacted after Intermediate deductions of Expenses and Taxes are applied, and finally showing Net Income as Sub Total.

                    Distribution Plot Chart

To Show Range and Distribution of groups of numerical data

Data is plotted as Value points and Bounding box highlights the range of distribution.

Stretched box shows high distribution compared to Compressed box with low distribution.

Box color and point color can be changed accordingly.

Example below shows how Sales are distributed for each Sub Category (Represented as box stretch) and across each Quarter (Represented as Value points).

                         Histogram Chart

Shows distribution of numerical data over a continuous interval.

Data is organized into bins and height of bin represents frequency.

Grouping of data into bins can be customized by adjusting the Width of bars or Setting number of bars to display.

Below example shows temperature frequency of a Country in an year. (Bar Width set to 2 and so for every 2 degrees a bin is created).

                                Box Plot Chart

Provides Range and Distribution of Numeric Data

Components of Box Plot

Whiskers- Represents Reference value which is a Quartile, Percentile or Standard Dev based.

Box- Represents First and Third Quartile values with a Median Line.

Outliers- Represents data points falling outside the Whisker range.

Below example of box Plot Chart shows distribution of Sales by Month.

                    Maps visualizations 

Qlik Sense April 2018 features significant improvements to the built-in maps visualization

Support for multiple layers.

Labels for point layers and area layers

Quick look up of countries, divisions, cities,

     postal code areas

No fixed upper limit of number of objects,

     number of bubbles on area layer

Circle select with distance measure

Drill down support

Installing Qliksense

Once Prerequisites are completed, proceed to install the Qliksense latest version downloaded from Qlik.com. Please see below are the steps to download software

Open Qlik.com site on your browser. Below is the home page and click on login on Right side

Once clicked on login page, you will get this page

Please enter credentials and click on customer downloads on the page and you can see customer downloads and there select the options based on your requirement. to give example here, am selecting qliksense server April 2018 below. please find below image

Once software is downloaded, Place the qliksense software on server desktop and start installation

Please right click on software and run as administrator and then click on yes

once click on yes, you will get an option to Join cluster. Please find below image

Once clicked on Join cluster, you will get an option to give database details. Please give user password and keep it safe for future use.Please find below image

As discussed in earlier post, for shared persistence we need a shared folder. whatever folder created as part of prerequisites, Please give that folder name starting with server name. This folder should be shared after creating by right clicking on the folder and share it to the Functional account. Please find below image

Please put default directory as C drive to put installation files. Please find below screen shot

Once this is done, it will ask for Functional account and please give those details

Once this is given, Installation run its on own and it will show as complete

Administering Qliksense

Administering Qliksense:

This contains information on how to deploy qliksense on single node.Before proceeding to install,consider the below prerequisites.

We have to set up a functional account to run qliksense services going to install on server. After creating the functional account,it needs to be added in the administrators group located in the computer management. Once it is added, the account  has complete access to the server. Please find below screen shot shows where functional account needs to be added

Earlier qilksense used to release the two types of installations synchronized and shared persistence. As there were some issues with synchronized persistence qliksense stopped releasing sync persistence from 3.2 version. Now we have only shared persistence installation type. Let me give a brief introduction on sync and shared persistence.(shared persistence has only database installed only on central node and it has shared by slave/rim nodes if there are any where as sync persistence has database installed on each node)

For shared persistence type, We need to create a shared folder to store repository files. This should be created on central node and should be shared. The path needs to be given in one place as part of installation. Will explain installation in the next post.