topic Re: Correct way to create a dimension from data in separate columns? in App Development

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

Thu, 04 Feb 2016 19:26:53 GMT

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

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

maxgro — Thu, 04 Feb 2016 19:50:13 GMT

you can try in SQL with a (pseudocode)

SQL

select distinct

PersonID,

Gender,

Zipcode

from yourtable;

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

DROP table TMP;

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

Thu, 04 Feb 2016 19:54:52 GMT

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.

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

maxgro — Thu, 04 Feb 2016 20:06:12 GMT

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

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

paul_scotchford — Thu, 04 Feb 2016 21:22:20 GMT

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

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

Fri, 05 Feb 2016 05:21:21 GMT

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

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

Fri, 05 Feb 2016 15:39:09 GMT

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

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

maxgro — Fri, 05 Feb 2016 19:47:40 GMT

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.

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

Fri, 05 Feb 2016 20:09:27 GMT

Thanks!

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

paul_scotchford — Fri, 05 Feb 2016 21:05:56 GMT

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