Correct way to create a dimension from data in separate columns?

[#1]

If I need to create a dimension out of data that is naturally in separate columns do I do this with SQL before importing it or is there a way to import the data as is and then create a dimension in QlikSense?

Links to examples or discussions of this issue are much appreciated.

Here is an example.

Data Fields:

  PersonID

  Gender

  Zipcode

  $SpentOnEntertainment

  $SpentOnFood

  $SpentOnTravel

  $SpentOnHealthCare

  $SpentOnHousing

I want to create a pie chart where the segments are Entertainment, Food, Travel, HealthCare, Housing.

I know I can use SQL to transform into two tables and import them.

  PersonID

  Gender

  Zipcode

and

  PersonID

  Category --Values Entertainment, Food, Travel, HealthCare, Housing

  AmtSpent

Is this the correct/natural way to do it, or would import the original table and then do something in QlikSense? 

Thanks

[maxgro]

you can try in SQL with a (pseudocode)

P:

SQL

select distinct

PersonID,

  Gender,

  Zipcode

from yourtable;

T:

SQL

  select PersonID, 'Entertainment' as Category , $SpentOnEntertainment as AmtSpent from yourtable

UNION all select PersonID, 'Food' as Category , $SpentOnFood as AmtSpent from yourtable

UNION all select PersonID, 'Travel' as Category , $SpentOnTravel as AmtSpent from yourtable

UNION all select PersonID, 'HealthCare' as Category ype, $SpentOnHealthCare as AmtSpent from yourtable

UNION all select PersonID, 'Housing' as Category , $SpentOnHousing as AmtSpent from yourtable

;

the same in Sense

TMP: SQL select * from yourtable;

P: load distinct PersonID,  Gender,  Zipcode from TMP;

T: load PersonID, 'Entertainment' as Category , $SpentOnEntertainment as AmtSpent resident TMP;

concatenate (T) load PersonID, 'Food' as Category , ..............

concatenate (T) load ..............

concatenate (T) load ..............

concatenate (T) load ..............

DROP table TMP;

[#3]

MG, I already know how to do it in SQL before I load the data.  My question is whether that is the preferred way or can you transform the data after bringing it into QlikSense?  Thanks.

[maxgro]

I prefer to keep the ETL in one place, if possible.

When I use Qlik, I usually keep all the transformation in Qlik,  not in the DBMS (SQL).

[paul_scotchford]

Every site will have a preferred method for ETL, IMHO do the heavy lifting in your Data Warehouse or Mart when looking at large data sets, ETL in SQL (SSIS) for example is more powerful and the data is then available to use across the corp by other consumers, not all sites are purely Qlik, e.g. I have a client with QV,QS and Cognos , their DataWarehouse is  DB2, so the majority of data resides there for general consumption,

Then of course enter the ESB (Enterprise Service Bus) , another topic another day

[#6 · Accepted solution]

Hi Randy,

You can transform columns into rows using Cross Table load in QlikSense.

Use the newly created column as dimension.

Check this link by HIC on Cross Table Load

https://community.qlik.com/blogs/qlikviewdesignblog/2014/03/24/crosstable

Regards,

Rohan

[#7]

MG & Paul, thanks both for your replies.  I phrased  my question badly so let me rephrase it.  If I import data into Qlik Sense that looks like

  PersonID |  Gender |  Zipcode |  $SpentOnEntertainment |  $SpentOnFood   $SpentOnTrave l   $SpentOnHealthCare   

     1    ....

     2    ....

     3    ....

Can i transform the $Spent columns in inside Qlik Sense into a single dimension of categories of spend?   Or does that transformation always have to happen outside Qlik Sense (either during ETL or in the DW)?

Thanks

[maxgro]

I prefere the script way

But maybe you can use a calculated dimension

ValueList('E', 'F', 'H', 'T')

with an expression like

pick(match(ValueList('E', 'F', 'H', 'T'),

  'E', 'F', 'H', 'T',

  ),

  sum($SpentOnEntertainment),

  sum($SpentOnFood),

  sum($SpentOnHealthCare),

  sum($SpentOnTravel)

  )

The script way seems simpler.

[#9]

Thanks!

[paul_scotchford]

Both the suggestions by Rohan and MG would be suitable for your needs. I would recommend you review the

link Rohan provides for a paper by Henric C.

My personal preference is for using crosstable in your load script

Cheers

Paul