ICRT File Monitor Informatica Cloud Real Time Connection !

Dynamic Drill Down Hierarchy Using Set Actions Implementation in Tableau

December 4, 2020

Problem Description :

Migration from Qlik Sense to Tableau, now day’s Tableau is growing very faster in the Data Science field and it is more related on Data Analytics platform, it can convert the raw data into an understandable format without any coding and technical skills. Migrate from Qlik Sense to Tableau We can use it to generate reports, dashboards, and analysis of the huge data from multiple sources, but Qlik Sense is a self-service data discovery and analysis for data modelling and data management.

Challenges to User/Developer-

Migrate from Qlik Sense to Tableau: To come across Qlik to Tableau migration, data migration we need to cross several complex conversion phases and we need to understand the component of each tool as well as data.

Migrate from Qlik Sense to Tableau For implementing or deploying Qlik Sense, it is better to seek help and hire Qlik consultants. Whereas, in Tableau, you need to be specific about the services you require under a Tableau version as the corresponding Tableau server needs to be configured for appropriate functions.

Solution

Migrate from Qlik Sense to Tableau:

Approach-

The migration will have several stages such as Data Analysis, Data Model, Data Conversion, and Data visualization.

Step by Step Procedure to migrate from Qlik Sense to Tableau:

Step 1: Data Analysis

Migrate from Qlik Sense to Tableau : We need to understand what kind of data is existed in the database (SQL Server) to convert it into another database (Snowflake).

Sample:

SQL Server:

SELECT

A.*,

(Case when CATEGORY = ‘ technology’ then ‘Office Supplies ‘ else CATEGORY end) as F_CATEGORY,

CAST (c. CREATEDDATE as date) AS O_CREATED_DATE,

Month_Name AS O_CREATED_MONTH,
Fiscal_Year AS O_CREATED_FS_YEAR,

(d.Fiscal_Year_Full_Name + ‘-‘ + d.Fiscal_Quarter) as O_CREATED_FS_QTR,

(CAST (d.Year AS NVARCHAR) + ‘-‘ + CAST (d.Month_Number_of_Year AS NVARCHAR) + ‘ (‘ + d.Month_Name_Abbr + ‘)’) as O_CREATED_FS_MONTH,

(Case when d.Fiscal_Year >= ‘2018’ then ‘O_COUNT_Y’ else ‘O_COUNT_N’ end) as O_DISPLAY_COUNT,

(Case when e.SALES_TYPE is null then ‘New Sale’ else e.SALES_TYPE end) as SALES_TYPE

FROM

DEMO_DB.demo.F_demo_ OPPORTUNITY_SNAP a

Left Outer Join DEMO_SF.Finance.ACCOUNT b on a.ACCOUNT_ID = b.ID

Left Outer Join DEMO_SF.Finance.OPPORTUNITY c on a.OPPORTUNITY_ID = c.ID

Left Outer Join DEMO_DB.cal.D_Calendar d on CAST (c.CREATEDDATE AS DATE) = d.Date_Full

Left Outer Join DEMO_DB.demo.D_demo_SALES_TYPE e on e.OPPORTUNITY_ID = a.OPPORTUNITY_ID and e.SK_ID = a.SK_ID

WHERE

a.CATEGORY <> ‘Furniture’

And a.OPPORTUNITY_SPLIT_TYPE = ‘Revenue’

And b.[NAME] not like ‘REV%’

And a.SNAP_DK >= ‘20200329’

And a.DATA_CODE <> ‘Exclude’;

Snowflake:

SELECT

A.*,

(Case when CATEGORY = ‘technology’ then ‘Office Supplies ‘ else CATEGORY end) as CATEGORY,

CAST (c.CREATED DATE as date) AS O_CREATED_DATE,

D.Month_Name AS O_CREATED_MONTH,

D.Fiscal_Year AS O_CREATED_FS_YEAR,

(d.Fiscal_Year_Full_Name || ‘-‘ || d.Fiscal_Quarter) as O_CREATED_FS_QTR,

(CAST (d.Year AS VARCHAR) || ‘-‘ || CAST (d.Month_Number_of_Year AS VARCHAR) || ‘ (‘ || d.Month_Name_Abbr || ‘)’) as OPP_CREATED_FIS_MONTH,

(Case when d.Fiscal_Year >= ‘2018’ then ‘O_COUNT_Y’ else ‘O_COUNT_N’ end) as O_DISPLAY_COUNT

FROM

“DEMO_DB”.”Demo”.” OPPORTUNITY_SNAP” a

Left Outer Join “DEMO_DB “.”Finance”. “ACCOUNT” b on a.ACCOUNT_ID = b.ID

Left Outer Join “DEMO_DB “.” Finance “.”OPPORTUNITY” c on a.OPPORTUNITY_ID = c.ID

Left Outer Join “DEMO_DB “.”Demo”.”D_CALENDAR” d on CAST (c.CREATEDDATE AS DATE) = d.Date_Full

WHERE

a.FORECAST_CATEGORY <> ‘Furniture’

And a.OPPORTUNITY_SPLIT_TYPE = ‘Revenue’

And b.NAME not like ‘REV%’

And a.SNAP_DK >= ‘20200329’;

Step 2: Data Model

After converting complex custom SQL queries from one database to another database then we need to re-design and develop a similar kind of data model from Qlik to Tableau which may consist of either base tables or custom SQL queries.

Qlik Sense model:

Tableau model:

Migrate from Qlik Sense toTableau model

Step 3: Data Conversion

Once this data model is done at the data source level, we need to take care of some of the complex calculated conversion from Qlik to Tableau which should perform a similar kind of functionality.

When we are migrating from Qlik to Tableau some of the functionalities which work on Qlik will not work in Tableau, so we need to convert it by using proper functions.

Qlik Sense calculation:

If (

Sum ({%%EDITORCONTENT%%lt;

[Revenue.SourceType]= {‘Office Supplies’},

[Revenue.Snap_key] = {‘$(=getfieldselections (Revenue.Snap_key))’},

[Revenue.MonthSeries] = {‘$(=if (([Revenue.Snap_key] =’$(=getfieldselections (Revenue.Snap_key))’), ([Revenue.MonthSeries])))’

,’$(=if (([Revenue.Snap_key] =’$(=getfieldselections (Revenue.Snap_key))’), ([Revenue.MonthSeries]-1)))’

,’$(=if (([Revenue.Snap_key] =’$(=getfieldselections (Revenue.Snap_key))’), ([Revenue.MonthSeries]-2)))’

………

………..

…………

,’$(=if (([Revenue.Snap_key] =’$(=getfieldselections (Revenue.Snap_key))’), ([Revenue.MonthSeries]-n)))’

}

>} [Revenue. Revenue]/1000000)

Sum ({%%EDITORCONTENT%%lt;

[Revenue.SourceType]= {‘Technology’},

[Revenue. Type] = {‘HC_Revenue’},

[Revenue.Snap_key] = {‘$(=getfieldselections (Revenue.Snap_key))’},

[Revenue.MonthSeries] = {‘$(=if (([Revenue.Snap_key] =’$(=getfieldselections (Revenue.Snap_key))’), ([Revenue.MonthSeries] +1)))’

,’$(=if (([Revenue.Snap_key] =’$(=getfieldselections (Revenue.Snap_key))’), ([Revenue.MonthSeries] +2)))’

……………

…………..

,’$(=if (([Revenue.Snap_key] =’$(=getfieldselections (Revenue.Snap_key))’), ([Revenue.MonthSeries] +n)))’

}

>} [Revenue. Revenue]/1000000)>0,

Sum ({%%EDITORCONTENT%%lt;

[Revenue.SourceType]= {‘Office Supplies’},

[Revenue.Snap_key] = {‘$(=getfieldselections (Revenue.Snap_key))’},

[Revenue.MonthSeries] = {‘$(=if (([Revenue.Snap_key] =’$(=getfieldselections (Revenue.Snap_key))’), ([Revenue.MonthSeries])))’

,’$(=if (([Revenue.Snap_key] =’$(=getfieldselections (Revenue.Snap_key))’), ([Revenue.MonthSeries]-1)))’

,’$(=if (([Revenue.Snap_key] =’$(=getfieldselections (Revenue.Snap_key))’), ([Revenue.MonthSeries]-2)))’

……………..

,’$(=if (([Revenue.Snap_key] =’$(=getfieldselections (Revenue.Snap_key))’), ([Revenue.MonthSeries]-n)))’

}

>} [Revenue. Revenue]/1000000)

Sum ({%%EDITORCONTENT%%lt;

[Revenue.SourceType]= {‘Technology’},

[Revenue. Type] = {‘HC_Revenue’},

[Revenue.Snap_key] = {‘$(=getfieldselections (Revenue.Snap_key))’},

[Revenue.MonthSeries] = {‘$(=if (([Revenue.Snap_key] =’$(=getfieldselections (Revenue.Snap_key))’), ([Revenue.MonthSeries] +1)))’

,’$(=if (([Revenue.Snap_key] =’$(=getfieldselections (Revenue.Snap_key))’), ([Revenue.MonthSeries] +2)))’

………………

,’$(=if (([Revenue.Snap_key] =’$(=getfieldselections (Revenue.Snap_key))’), ([Revenue.MonthSeries] +n)))’

}

>} [Revenue. Revenue]/1000000))

Tableau calculation:

Global Revenue_1:

SUM (IF [Source type] = ‘Office Supplies’

THEN

[Revenue] END)

+ Sum (IF [Source type] = ‘Technology ‘and [Type] = ‘HC_Revenue ‘THEN ([Revenue]) END)

Global Revenue_2:

SUM (IF [Source type] = ‘Office Supplies’

THEN

[Revenue] END)

+sum (IF [Source type] = ‘Technology ‘and [Type] = ‘HC_Revenue’ THEN [Revenue] END)

Global Revenue:

(IF [Global Revenue_1] > 0 THEN [Global Revenue_2] END)/1000000

Month Series minus Calculation:

Case ([Month series])

When

INT ([Snap key Set]) Then ([Month Series])

When

INT ([Snap key Set]) Then ([Month Series]-1)

When

INT ([Snap key Set]) Then ([Month Series]-2)

……………

When

INT ([Snap key Set]) Then ([Month Series]-n)

END

Month Series plus Calculation:

Case ([Month series])

When

INT ([Snap key Set]) Then ([Month Series])

When

INT ([Snap key Set]) Then ([Month Series] +1)

When

INT ([Snap key Set]) Then ([Month Series] +2)

When

……….

INT ([Snap key Set]) Then ([Month Series] +n)

END

Step 4: Data Visualization

When it comes to data visualization, we need to take care below steps:

Formatting
Creating of worksheet
Filters
Calculated fields
Set Actions
Parameters
Dashboard alignment & layout
Story
Publishing into Tableau online
User roles and privileges

Qlik Dashboard:

Migrate from Qlik Sense to Tableau : Qlik Dashboard

Tableau Dashboard:

Migrate from Qlik Sense toTableau Dashboard

Advantages-

Qlik Sense is on the data discovery and analysis platform, but Tableau is more about on Data Analytics and Visualization platform.

Conclusion-

When we are planning to migrate the entire code and interactive dashboards from Qlik Sense to Tableau above solution will be more effective, flexible, and user-friendly.[/vc_column_text][/vc_column][/vc_row]