pivot-table,qlikview , QlikView : get data filtered in pivot table


QlikView : get data filtered in pivot table

Question:

Tag: pivot-table,qlikview

I found a great tutorial for building a nicer (dynamic) multibox, without extensions.

I was able to use it, and I really love it. However, I have an issue: if I use a multibox with a master detail table, then if I filter it from detail then master will automatically selected. The below example shows a normal multibox at the top, and dynamic/pivot multibox at the bottom:

enter image description here

How I can achieve this with my dynamic multibox?

For the label, I use:

=if(IsNull(GetFieldSelections(master)) = -1, 'Master',GetFieldSelections(master))

with this, I can set 'Master' as the label.

I can see data has seen filtered but I didn't find a function to get data that has been filtered to set it in the label, maybe someone here can give me an advice to solve this.


Answer:

The GetFieldSelections() will only give you the list of explicitly selected items. By selecting items in the detail, you're only selecting from the master implicitly.

Try using Concat() instead. This will give you the list of possible values for a field that isn't being directly selected upon.

Your next big headache will be how to determine when to show 'Master' or not. One possible method is to compare the full list of possible values in Master to the possible list. In the example below, I do this by comparing the possible count of master count(master) with the full list of possible values in master count({1}master).

Combining these, one solution could be:

=if(count(master)=count({1}master),'Master',Concat(master,', '))

Related:


QlikView Resources for Beginner Developer


business-intelligence,data-analysis,qlikview,qliksense
I am looking to add Qlikview Development to my skill-set. I have a C# and SQl background. Are there any free online resources to getting me going at developer level not end-user? What's the best starting place for me and the level of difficulty involved. I have seen the capabilities...

Pivot Table with multiple rows all having the same level hierarchy


excel,reporting,pivot-table,powerpivot
I have imported a bunch of data using PowerQuery into a single table and am building dashboard reporting. I have been using Pivot Tables to build my reports, which has worked fine so far. However, I've come to a point though where I want to simply show the count of...

Extract matching data from varying number of row


google-spreadsheet,spreadsheet,pivot-table
I am trying to build graphic indicators from a (potentially) large set of data using google-spreadsheet. So far, I've used a pivot table to extract the information from the raw data, and I want to build intermediate tables to calculate the different values I need for my indicators. So far,...

VBA - test if a value is a valid selection for a PivotField


excel-vba,excel-2007,pivot-table
For a pivot table (pt1) on Sheet1, I use VBA to change the value of a filter field (filterfield) using the code below. Let's say values for field can be A, B or C Sheets("Sheet1").PivotTables("pt1").PivotFields("filterfield").CurrentPage = "A" Occasionally, and let's say randomonly for the purposes of this question, A, B...

Pivoting a single field in a table


sql-server,pivot-table
I have a table of the following structure: Name Type Line UniqueID Key Value I need to select all the fields except UniqueID field and add a key 'UniqueID' and a Value field with the value of the UniqueID to the result set. For example if I have three records:...

Pivot Table to Reorganize Data


excel,excel-formula,pivot-table
I have data in the below format However, I need to convert it into the below format using pivot tables or other formuals such as where 2015 row is just split by months (divided by 12). Kindly help me with a logic that I can use. Thanks in advance for...

Sort entire Pivot table in Excel 2013 by column


sorting,excel-2010,pivot-table,excel-2013,powerpivot
I have a Pivot Table with many columns. I want the Pivot the ability to sort one of the columns in a way that the whole column is sorted and not the relative position in the hierarchy. Example: NAME PRODUCT SUM Joe A 400 Joe B 200 Joe B 300...

Qlikview document reload fails due to authorization restrictions on SharePoint 2010


sharepoint,sharepoint-2010,qlikview
The situation I have created a Qlikview document based on SharePoint 2010 list data. The document is working just fine and I am able to reload data from my desktop, and ready to deploy to our Qlikview server. The problem However, when I run a script ("C:\Program Files (x86)\QlikView\Qv.exe" /r...

Load connecting tables from Cassandra in QlikView with DataSatx ODBC


cassandra,datastax,qlikview
I am new to both Cassandra (2.0) and QlikView (11). I have two keyspaces (tables) with large amount of data in Cassandra and I want to load them to QlikView. Since I can not load the entire set, filtering is necessary. // In QlikView's edit script ODBC CONNECT TO [DataStax...

Excel PivotTable; how to show values horizontally


excel,pivot-table
I'm trying to rearrange a pivot table that organizes all values (not sum or other statistic) from an original table. Seems simple but I can't find a way to make it values rather than sums. My original data looks like: Rank Name 1 A 1 B 2 C 2 D...

How to have multiple columns on Pivot tables?


sql-server,join,sql-server-2012,pivot-table,common-table-expression
I have a table in my sql server called, VendorItemPricing. select * from VendorItemPricing where ItemID = 122 And this query will give me the below results. ItemID VendorName VendorPrice UpdatedDate ObsoleteItem 122 HP 215.13 2015-05-15 11:55:49.983 0 122 IBM 264.41 2015-05-15 11:56:04.990 0 122 Microsoft 257.65 2015-05-15 11:56:23.963 0...

R: output a pivot-like table with subtotals


html,r,pivot-table,rmarkdown,xtable
I'm trying to make a cross tabulation in R, and having its output resemble as much as possible what I'd get in an Excel pivot table. The objective is to replace a report made manually with Excel and Word with one automated with R Markdown; data wrangling and charts have...

Sum Colum case () after pivot


sql-server,sql-server-2005,pivot-table
Sorry if I don´t explain very well, and the title isn´t very clear. I´m using SQL Server 2005. I have a query with Pivot that is working fine, but know I must add a new query that get resuls from quarterly. This is my query to get result from Month...

creating a calculated field in excel pivot table based on an item in a column


excel,pivot-table,calculated-field
Normally calculated pivot table fields in excel (2010) you click: design tab > options and sets > calculated field and if you had three columns: name, sales, cost name | sales | cost josh 10 2 your calculated field might be: profit which would be: = sales - cost. However...

VBA to change Pivot Filter in OLAP cube to a range


excel,vba,excel-vba,pivot-table,olap-cube
I have a pivot table that pulls data from an OLAP cube, and I'd like to create a macro to filter a pivot field that contains 'Week of Year' based on the value in another cell, so that I can easily change the time frame of the table. My experience...

What is the syntax for pivot tables in Pandas? Docs don't seem to be right?


python,pandas,pivot-table
I might be totally crazy, but I'm reading the docs for pivot_table in Pandas, and even some guides Literally using the example from the docs with my own data: import pandas as pd df = pd.read_csv('data.csv') pivot_table(df, values='D', index=['A', 'B'], columns=['C'], aggfunc=np.sum) Produces an error: pivot_table(df, values='D', index=['A', 'B'], columns=['C'],...

SQL Server table manipulation


sql-server,table,structure,pivot,pivot-table
I am really new here. Currently working on SQL Server 2012. I have table A of the following structure (records are a subset of the full data table): CREATE TABLE [dbo].[TABLE_A]( [ASSET ID] [float] NULL, [TASK ID] [float] NULL, [IN_YEAR ] [float] NULL, [IN_WEEK] [float] NULL, [FLAG] [nvarchar](1) NULL )...

QlikView : get data filtered in pivot table


pivot-table,qlikview
I found a great tutorial for building a nicer (dynamic) multibox, without extensions. I was able to use it, and I really love it. However, I have an issue: if I use a multibox with a master detail table, then if I filter it from detail then master will automatically...

How to use PIVOT and JOIN together in SQL Server?


sql-server,join,sql-server-2012,pivot-table
This is table 'VendorItemPricing'. ItemID VendorName VendorPrice 122 HP 215.13 122 IBM 264.41 122 Microsoft 257.65 I used this query to get make rows as columns. Select ItemID, [HP] As HPPrice , [Apple] As ApplePrice, [Microsoft] As MicrosoftPrice, [IBM] As IBMPrice from ( select ItemID,VendorName,VendorPrice from VendorItemPricing where ItemID =...

Excel pie charts from pivot table columns


excel,charts,pivot-table
I have a pivot table from a database that looks something like this: Person A Person B Person C Task A 1 3 Task B 1 2 Task C 2 1.5 1 The number is time spent on a task. If it's blank, the person didn't work on that task....

Qlikview Substring and charindex equivalent to show values after the - character


qlikview,qliksense
I have a field which has the values field good - examplea good - exampleb bad - examplep ugly - examplet ugly - exampley I want to only show values after the - character. My example output would be field examplea exampleb examplep examplet exampley In SQL it would be...

Load multiple Excel sheets using For loop with QlikView


qlikview
I want to load data from two different Excel files, and use them in the same table in QlikView. I have two files (DIVISION.xls and REGION.xls), and I'm using the following code: let tt = 'DIVISION$' and 'REGION$'; FOR Each db_schema in 'DIVISION.xls','REGION.xls' FOR Each v_db in $(tt) div_reg_table: LOAD...

Qlikview line chart with multiple expressions over time period dimension


qlikview
I am new to Qlikview and after several failed attempts I have to ask for some guidance regarding charts in Qlikview. I want to create Line chart which will have: One dimension – time period of one month broke down by days in it One expression – Number of created...

What is the difference between pivottable.refreshtable and pivottable.update?


excel,excel-vba,pivot-table
What is the difference between the methods pivottable.refreshtable and pivottable.update? I have been googleling and haven't figured out if pivottable.refreshtable is enough to update a pivottable or if pivottable.update also does something significant....

Qlikview Current selections box to use labels rather than table.fieldnames


qlikview,qliksense
In my Qlik View document I want to change the Current Selections information to use the Label applied to the field rather than the table.field format. For example PartsTable.PartNo Would be Part Number ...

Excel VBA Pivot Cache Type Mismatch Run-Time Error '13'


excel-vba,runtime-error,pivot-table
Thanks for any input on this. I'm trying to make a simple pivot table that is taking data from sheet "5 Month Trending May 15" and putting it onto my Pivot Table sheet called "Errors By Criticality - Pivot". When I try to set the pivot cache range with the...

Pivot SQL table (Rows into Columns)


sql,sql-server,table,pivot,pivot-table
I am currently returning data into a table in my SQL stored procedure. I am trying to pivot the rows into columns, and the columns into rows, but I am struggling to do so as a lot of the tutorials I am following to do this have laid out they're...

How to select a particular number in a list box and make the other list box to only display the numbers greater than the selected number in QlikView?


business-intelligence,dashboard,qlikview,dashboard-designer,qliksense
I have two list boxes: Box1 and Box2. Box1 has values 10,20,30,40 etc and Box2 has the same 10,20,30 etc. After creating two separate list boxes for Box1 and Box2 respectively in the dashboard, how do I get to show only the values in Box2 which are greater than the...

Laravel 4 - Scope to only show rows where another DB has no relational items


laravel-4,eloquent,pivot-table
I have a cars table, and a car_image table. I want to get all the cars that have NO images in the car_image. In my car model I am making a scope: public function scopeNoImages($query) { return $query-> ?? } How can I create a scope that will show only...

Pivot Tables or Group By for Pandas?


python,pandas,count,group-by,pivot-table
I have a hopefully straightforward question that has been giving me a lot of difficulty for the last 3 hours. It should be easy. Here's the challenge. I have a pandas dataframe: +--------------------------+ | Col 'X' Col 'Y' | +--------------------------+ | class 1 cat 1 | | class 2 cat...

Qlikview GetFieldSelections Multiple Selection


qlikview
I have a list box that selects distinct values (one of three) from a listbox. This then filters a chart where I show a bar chart based on sum totals. Currently, it works when one value is selected from the listbox, I require the chart to work with more than...

If a date is 22/12/2015 and has a value of 10, how to fill the value 10 to all the other dates following 22/12/2015 in qlikview?


qlikview,qliksense
I have two tables [ORDERS TABLE] and [PRICE LIST TABLE]. [ORDERS TABLE] contains the fields: OrderDate, ItemCode, Quantity. [PRICE LIST TABLE] contains the fields: Date, ItemCode, Price. Now the [PRICE LIST TABLE] explains the price of certain items during certain dates. It hardly consists of 10 - 15 records. For...

What does the SSAS setting None in AggregateFunction do?


excel,ssas,pivot-table,cube
As I understand Microsoft's explanation setting the AggregateFunction of a measure to "None" should null out the value unless you are viewing the data at the lowest possible level. (i.e. the data element is a copy of the value in one exact cell in the cube). I've created a fact...

How do I sum results of two if statements in the same cell?


excel,if-statement,excel-formula,excel-2010,pivot-table
I'm looking to have the results of two If statements calculated and added in the same cell. I'm getting #VALUE! error. =IF(ISERROR(GETPIVOTDATA("Sum of CHARGES",'Ship City'!$A$3,"ship_city",$B$7,"carrier_type",$A$27,"INV_month_id",D$6,"INV_year_id",$D$5,"Company Name",$B29)),"",GETPIVOTDATA("Sum of CHARGES",'Ship City'!$A$3,"ship_city",$B$7,"carrier_type",$A$27,"INV_month_id",D$6,"INV_year_id",$D$5,"Company Name",$B29))+IF(ISERROR(GETPIVOTDATA("Sum of CHARGES",'Recipient...

Can I use Laravel 5 functionality to save positions per company per user?


php,jquery,mysql,laravel-5,pivot-table
This is my first question so please excuse any protocol errors! I am also new to Laravel 5. I have watched the Fundamental video series (especially "syncing tags" and have that working for simple pivot tables) and have searched the forum and further online but am not actually even sure...

How to select SQL Server row values as columns (Dynamic Pivot)


sql,sql-server,pivot,pivot-table
I have one table where we are collecting status data from different sensors. For reporting purposes I would need to group information for each sensor (SGUID) to one row. Each sensor is sending reader (RGUID) and value for reader (VAL). Source table: Goal result: I have heard about pivot's but...

How to display comparisons with set expression?


qlikview
My dataset has WeekEndingDate and Sales. I am displaying a straight table with all the selected data but I need to have another table showing the following: Sales (other columns...) First week : 1,000 Last week : 1,350 Difference : 350 Difference %: 35% My questions: a) Can I have...

Qlikview Automate past 12 months selection


date,business-intelligence,qlikview,qliksense
I have a combo chart which shows the average days it took for a person to pay their bill. The Dimension of the Chart is = [Pay Month Year last 12 months] There are no Dimension limits There is 1 expression which is called Average and its definition is: avg({<...

Convert rows to columns in sql server?


sql-server,sql-server-2012,pivot-table
I'm looking for an efficient way to convert rows to columns in SQL server, I heard that PIVOT is what I'm searching for, and I'm new to pivot tables. This is my example: ItemID VendorName VendorPrice 122 HP 125.66 122 Apple 130.44 122 Microsoft 134.00 122 IBM 124.90 This is...

Derive two hours time blocks from start and end time fields


ms-access,pivot-table
I have a table in Microsoft Access that has a list of volunteers for an event. The table contains fields such as name, phone number, etc. There are three additional fields; Assignment which describes what activity the volunteer will be assigned to. For example, parking, front gate, ticket Booth, etc....

Simple pandas pivot table issue


python,pandas,pivot-table
I have a dataframe like below: +-------+--------+----------+---------------+ | | ios_id | video_id | feed_position | +-------+--------+----------+---------------+ | 11995 | user1 | 199 | 7 | | 11996 | user2 | 164 | 18 | | 11997 | user3 | 209 | 1 | | 11998 | user3 | 85 |...

How to count multiple values in one single cell using Pivot Tables in Excel?


excel,excel-formula,pivot-table
I have this table in a Excel sheet: I would like to create a Pivot Table to have this outcome: Do you guys have any idea how to do it? Thank you!...

YoY comparison using QlikView


qlikview
My data contains daily data from last March till now. I want to compare the sales amount of 2015/4 vs 2014/4 and 2015/4 vs 2015/3. My date format is 01,02,03,04,...,12 for Date_Month, and 2014,2015 for Date_Year. I first create two variables: vCurrentYear=max(Date_Year) vCurrentMonth=Max (Date_Month) Then I create a bar chart...

Pivot with ID and Dates


sql-server,sql-server-2008,pivot-table
I have this data set and Im trying to create a pivot table for the data below. I having some issues when trying to put in the date for each type across. Member ID Type Date 1 A 12/5/2014 1 b 3/6/2014 2 a 6/9/2015 2 b 3/2/2015 2 c...

how to create Pivot query for this?


sql,sql-server,sql-server-2008,pivot-table,powerpivot
I need output like this. In this no limit of shape; for example : [RD],[PC],[EM],[SE],[PR],[CM] My data looks like this : POINTER_RANGE SHAPE_NAME PCS CTS VALUE 0.30-0.39 RD 6 1.826 3503.17 0.40-0.49 RD 3 1.238 2837.62 0.50-0.69 PR 1 0.504 776.16 0.50-0.69 RD 5 2.618 8639.58 0.70-0.89 PC 5 4.109...

Using multiple functions in the same field in QlikView


qlikview
I want to use multiple functions on a field and store the result into one field, like this: left(Campagne,len(Campagne)-4) and Replace(Campagne,'%2f','/') and PurgeChar (Campagne,'.g.c') as Campagne; How can I do this?...

How to construct a pivot table that contains values based on certain conditions


excel,vba,pivot-table,powerpivot
I want to construct a pivot table from a huge data-set. Now I want to construct the pivot table that consists of values based on certain specified conditions, for example, the entries with age>25 will only be populated in the pivot table. How to do that without manually filtering out...

(kdb+/q) Pivot table: date in rows, symbols in columns, last price as values


pivot-table,kdb,q-lang
General pivot function provided at http://code.kx.com/wiki/Pivot goes: piv:{[t;k;p;v;f;g] v:(),v; G:group flip k!(t:.Q.v t)k; F:group flip p!t p; count[k]!g[k;P;C]xcols 0!key[G]!flip(C:f[v]P:flip value flip key F)!raze {[i;j;k;x;y] a:count[x]#x 0N; a[y]:x y; b:count[x]#0b; b[y]:1b; c:a i; c[k]:first'[a[j]@'where'[b j]]; c}[I[;0];I J;J:where 1<>count'[I:value G]]/:\:[t v;value F]} given that f and g are defined as f:{[v;P]`$raze each...

Passing external parameter to QV script


qlikview
I am a newbie to QlikView and looking for some guidance on how to pass external parameter to qv script i.e qvw file. Below is the scenario on which I am working: We are creating a report for which the source is database and we will be using automation tools...