topic Re: How do i display all? in QlikView

topic Re: How do i display all? in QlikView https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470531#M175756 <HTML><HEAD></HEAD><BODY><P>Hi ,</P><P></P><P>thanks for the prompt reply</P><P>Yes i am using pivot table, due to the constraint and complexity of the data. I have to use on the dimension tab.</P><P></P><P>However the above code mention, i would want the 'others' to include 'london' data.</P></BODY></HTML> Fri, 19 Apr 2013 09:18:27 GMT n1ef5ng1 2013-04-19T09:18:27Z How do i display all? https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470525#M175750 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>Example, I have a field name call City which comprises all the cities all over the world</P><P></P><P>Firstly, I put this statement on the dimension tab</P><P></P><BLOCKQUOTE><TABLE border="1"><TBODY><TR><TD><P>if(city = 'london','london','Others')</P></TD></TR></TBODY></TABLE></BLOCKQUOTE><P></P><P>This will tabulate two column, London and Others(All cities exclude London)</P><P></P><P>However, here is the twist,</P><P></P><P>My desired output is two columns with London and Others(All including London)</P><P></P><P>How do I derived this, kinda urgent. Thank you </P></BODY></HTML> Fri, 19 Apr 2013 09:04:32 GMT https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470525#M175750 n1ef5ng1 2013-04-19T09:04:32Z Re: How do i display all? https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470526#M175751 <HTML><HEAD></HEAD><BODY><P>the first idea I have is:</P><P>add another field in the script equal to city, and call it city1 (for example)</P><P></P><P>then load again and use the second field as another dimesion ... use the condition </P><P>if(city = 'london','london',null()) only on the first selection and exclude null values ...</P><P></P><P>hopemit helps</P></BODY></HTML> Fri, 19 Apr 2013 09:11:38 GMT https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470526#M175751 alexandros17 2013-04-19T09:11:38Z Re: How do i display all? https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470527#M175752 <HTML><HEAD></HEAD><BODY><P>Hi</P><P></P><P><SPAN style="color: #737373; font-family: Arial; background-color: #ffffff;">if(city = 'london',city,'Others')</SPAN></P><P></P><P><SPAN style="color: #737373; font-family: Arial; background-color: #ffffff;">this condition gives 2 values for city london and others</SPAN></P><P></P><P><SPAN style="color: #737373; font-family: Arial; background-color: #ffffff;">if u need these as columns then use pivote table and drag and drop city field on the top of tab.<BR /></SPAN></P></BODY></HTML> Fri, 19 Apr 2013 09:13:59 GMT https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470527#M175752 2013-04-19T09:13:59Z Re: How do i display all? https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470528#M175753 <HTML><HEAD></HEAD><BODY><P>don't exclude null values, I was wrong ....</P><P></P><P>the second solutopm, from arya brahma is really good but you have to use a pivot not a straight table</P></BODY></HTML> Fri, 19 Apr 2013 09:15:37 GMT https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470528#M175753 alexandros17 2013-04-19T09:15:37Z Re: How do i display all? https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470529#M175754 <HTML><HEAD></HEAD><BODY><P>Hi ,</P><P></P><P>thanks for the prompt reply</P><P>Yes i am using pivot table, due to the constraint and complexity of the data. I have to use on the dimension tab.</P><P></P><P>However the above code mention, i would want the 'others' to include 'london' data.</P></BODY></HTML> Fri, 19 Apr 2013 09:18:14 GMT https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470529#M175754 n1ef5ng1 2013-04-19T09:18:14Z Re: How do i display all? https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470530#M175755 <HTML><HEAD></HEAD><BODY><P>Hi ,</P><P></P><P>thanks for the prompt reply</P><P>Yes i am using pivot table, due to the constraint and complexity of the data. I have to use on the dimension tab.</P><P></P><P>However the above code mention, i would want the 'others' to include 'london' data.</P></BODY></HTML> Fri, 19 Apr 2013 09:18:23 GMT https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470530#M175755 n1ef5ng1 2013-04-19T09:18:23Z Re: How do i display all? https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470531#M175756 <HTML><HEAD></HEAD><BODY><P>Hi ,</P><P></P><P>thanks for the prompt reply</P><P>Yes i am using pivot table, due to the constraint and complexity of the data. I have to use on the dimension tab.</P><P></P><P>However the above code mention, i would want the 'others' to include 'london' data.</P></BODY></HTML> Fri, 19 Apr 2013 09:18:27 GMT https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470531#M175756 n1ef5ng1 2013-04-19T09:18:27Z Re: How do i display all? https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470532#M175757 <HTML><HEAD></HEAD><BODY><P>Hi ,</P><P></P><P>thanks for the prompt reply</P><P>Yes i am using pivot table, due to the constraint and complexity of the data. I have to use on the dimension tab.</P><P></P><P>However the above code mention, i would want the 'others' to include 'london' data.</P></BODY></HTML> Fri, 19 Apr 2013 09:18:37 GMT https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470532#M175757 n1ef5ng1 2013-04-19T09:18:37Z Re: How do i display all? https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470533#M175758 <HTML><HEAD></HEAD><BODY><P>Hi ,</P><P></P><P>thanks for the prompt reply</P><P>Yes i am using pivot table, due to the constraint and complexity of the data. I have to use on the dimension tab.</P><P></P><P>However the above code mention, i would want the 'others' to include 'london' data.</P></BODY></HTML> Fri, 19 Apr 2013 09:18:49 GMT https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470533#M175758 n1ef5ng1 2013-04-19T09:18:49Z Re: How do i display all? https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470534#M175759 <HTML><HEAD></HEAD><BODY><P>*edit am using Pivot table</P></BODY></HTML> Fri, 19 Apr 2013 09:20:03 GMT https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470534#M175759 n1ef5ng1 2013-04-19T09:20:03Z Re: How do i display all? https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470535#M175760 <HTML><HEAD></HEAD><BODY><P>Try to use my solution, it works and it is easy to use ...</P></BODY></HTML> Fri, 19 Apr 2013 09:24:46 GMT https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470535#M175760 alexandros17 2013-04-19T09:24:46Z Re: How do i display all? https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470536#M175761 <HTML><HEAD></HEAD><BODY><P>(i have never added anything from the script before)</P><P></P><P>till date, i did all my stuff through dimension and expression</P><P></P><P>can you teach step by step, appreciate alot</P></BODY></HTML> Fri, 19 Apr 2013 09:26:51 GMT https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470536#M175761 n1ef5ng1 2013-04-19T09:26:51Z Re: How do i display all? https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470537#M175762 <HTML><HEAD></HEAD><BODY><P>you can do this:</P><P></P><P>if(city = 'london',City) // this will be london only</P><P>if(city = '*', 'All') // this will be all cities</P></BODY></HTML> Fri, 19 Apr 2013 09:34:58 GMT https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470537#M175762 calvindk 2013-04-19T09:34:58Z Re: How do i display all? https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470538#M175763 <HTML><HEAD></HEAD><BODY><P>You have loaded a field named city, you find it in a load or select script statement.</P><P></P><P>Copy the row and modify it as follows:</P><P></P><P>Example</P><P></P><P>Load (or SELECT)</P><P>...</P><P>...</P><P>,,,</P><P>city,</P><P>...</P><P>From (or Resident)</P><P>     ....</P><P>Where</P><P>     ....</P><P></P><P>the transform to </P><P></P><P>Load (or SELECT)</P><P>...</P><P>...</P><P>,,,</P><P>city,</P><P>city as new City,</P><P>...</P><P>From (or Resident)</P><P>     ....</P><P>Where</P><P>     ....</P><P></P><P>this double city informations and you can use it as another column</P><P></P><P>Hope it helps</P></BODY></HTML> Fri, 19 Apr 2013 09:35:43 GMT https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470538#M175763 alexandros17 2013-04-19T09:35:43Z Re: How do i display all? https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470539#M175764 <HTML><HEAD></HEAD><BODY><P>where do you put this? on the dimension? both if?</P></BODY></HTML> Fri, 19 Apr 2013 09:43:50 GMT https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470539#M175764 n1ef5ng1 2013-04-19T09:43:50Z Re: How do i display all? https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470540#M175765 <HTML><HEAD></HEAD><BODY><P>Try</P><P></P><P>Dimension : Valuelist('London','Others')</P><P>Expression : if(Valuelist('London','Others')='London',</P><P>                         sum({<city={'London'}>}field1),sum({<city={'*'}>}field1))</P><P></P><P>The {'*'} will be all cities where there is a value. {'*'} also excludes nulls from the calculation</P><P></P><P>Should give you what you looking for mate</P><P></P><P></P><P>Cheers,</P><P>Byron</P></BODY></HTML> Fri, 19 Apr 2013 09:51:44 GMT https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470540#M175765 2013-04-19T09:51:44Z Re: How do i display all? https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470541#M175766 <HTML><HEAD></HEAD><BODY><P>what is field1?</P></BODY></HTML> Fri, 19 Apr 2013 10:00:58 GMT https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470541#M175766 n1ef5ng1 2013-04-19T10:00:58Z Re: How do i display all? https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470542#M175767 <HTML><HEAD></HEAD><BODY><P>replace field1 with the field you have in your document that you want to sum or count i.e. Revenue, Cost et cetera</P></BODY></HTML> Fri, 19 Apr 2013 10:20:00 GMT https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470542#M175767 2013-04-19T10:20:00Z Re: How do i display all? https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470543#M175768 <HTML><HEAD></HEAD><BODY><P>thanks alot</P><P></P><P></P><P>Dimension : Valuelist('London','Others')</P><P>Expression : if(Valuelist('London','Others')='London',</P><P>                         sum({<city={'London'}>}field1),sum({<city={'*'}>}field1))</P><P></P><P>say if i want to have three value list instead, london, other, % of shares</P><P></P><P>how do i fix it on the expression?</P></BODY></HTML> Mon, 22 Apr 2013 01:32:23 GMT https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470543#M175768 n1ef5ng1 2013-04-22T01:32:23Z Re: How do i display all? https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470544#M175769 <HTML><HEAD></HEAD><BODY><P>Heya,</P><P></P><P>Valuelist if very resource intensive so always try and find alternative solutions if working with a really large dataset.</P><P></P><P>To add, just include it in the dimension separated by a comma e.g.</P><P></P><P>Dimension: Valuelist('London','Others','% of Shares')</P><P>Expression:</P><P></P><P>if(Valuelist('London','Others','% of Shares')='London',</P><P>     sum({<city={'London'}>}field1),</P><P>if(Valuelist('London','Others','% of Shares')='Others',</P><P>     null(),</P><P>if(Valuelist('London','Others','% of Shares')='% of Shares',</P><P>     num(sum({<city={'London'}>}field1)/sum(TOTAL {<city={'London'}>}field1),'#.00%')))</P><P></P><P>Hope that helps mate</P><P></P><P></P><P>Cheers,</P><P>Byron</P></BODY></HTML> Fri, 26 Apr 2013 11:13:08 GMT https://community.qlik.com/t5/QlikView/How-do-i-display-all/m-p/470544#M175769 2013-04-26T11:13:08Z