14 March 2022 Unpivoting Data Share this message Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question: The answers to this question were unanimous, and you were right: The correct answer is answer B: CrossTable(Quarter, Budget, 2) Since the pivot table is a great way to analyze data in excel, it is just a matter of time before you will come across one. Contrary to a straight table, it consists out of grouped values in which you can expand the rows as you please to show more detailed aggregations. However, the format of a pivot table makes it less than ideal for integrating directly into your data model. You will have to ‘unpivot’ the values. Unpivoting data is done using the CrossTable prefix This is done by using the CrossTable prefix. But how does this work? Let’s have a look at the table loaded into the example: The values are aggregated by Channel, Region and Quarter, where the quarters are shown in individual columns. If we load this table directly into Qlik without any transformations, we get a separate field for each quarter named 2022-Q1, 2022-Q2, etc, making this not an ideal way for an end user to analyze. So how do we solve this? As mentioned before the CrossTable Prefix. If we look at the official Qlik documentation the prefix is used as following: crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement ) There is an attribute field name, consisting of the attribute values,A data field name, consisting of the data values,And n, consisting of the number of qualifying fields preceding the table to be transformed. So how does it work? A lot of theory, but how does this work in practice? Let’s make things a bit more clear by taking the example table from the question, labeling the relevant columns and cells and unpivoting the data. The schematic below shows how this works: The labels for the different quarters, 2022-Q1, 2022-Q2, etc. become the contents for the attribute field Quarter.The values in the columns 2022-Q1, 2022-Q2, etc. become the content for the data field Budget.The names of the attribute and data fields can be arbitrarily chosen. We used Quarter and Budget because these best describe the contents of the fields.The first 2 columns, Channel and Region do not need to be transformed. We specify the value 2 for the number of qualifier fields. This means that only the columns after the first 2 columns will be ‘unpivoted’. This leads to the following prefix: CrossTable(Quarter, Budget, 2) That’s it. We look forward to seeing your comments and hope to see you again next Friday! In the meantime… Just a reminder that we have a webinar for PinIt for Qlik Sense on March 17th. With PinIt, everyone can get an immediate, integrated overview of the metrics that matter to them. We do this by letting you combine visualizations from all your Qlik Sense applications into personal mashups (or “pinboards”). There is no code, and no hassle. Register for the webinar here. How can we help? Feel free to contact us if you have any comments or questions. Call us Mail us 29 June 2022 LOOKUP Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question: The correct answer is C: Roller, UMW, Cobretti, Grotti Most of you have gotten this answer right and that is no surprise. Things like vertical lookup, […] Friday Qlik Test Prep Solution 22 June 2022 GROUP BY Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question: The correct answer is D. The problem stated in this question was that the data in the front end showed wrong results. By examining the table in […] Friday Qlik Test Prep Solution 15 June 2022 AGGR Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question: The correct answer is C: Avg(Aggr(Sum(NumberOfProducts * ProductPrice), OrderID)) To explain the answer let’s see what it is that is exactly wanted. We have a straight table […] Friday Qlik Test Prep Solution
29 June 2022 LOOKUP Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question: The correct answer is C: Roller, UMW, Cobretti, Grotti Most of you have gotten this answer right and that is no surprise. Things like vertical lookup, […] Friday Qlik Test Prep Solution
22 June 2022 GROUP BY Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question: The correct answer is D. The problem stated in this question was that the data in the front end showed wrong results. By examining the table in […] Friday Qlik Test Prep Solution
15 June 2022 AGGR Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question: The correct answer is C: Avg(Aggr(Sum(NumberOfProducts * ProductPrice), OrderID)) To explain the answer let’s see what it is that is exactly wanted. We have a straight table […] Friday Qlik Test Prep Solution