Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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.
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).
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
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
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
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.
Thanks!
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