topic Re: Help with dimension in pivot table in QlikView

Help with dimension in pivot table

Fri, 26 Jul 2013 07:08:18 GMT

Hi, guys,

I`m new to QlickView and could not find an answer to my question.

I`m crating a pivot table to categorize our clients by how many products they have

I`ve tried this in dimension but it doesn`t work

=If (Count(DISTINCT([productName]))>3, '4 products',

if (Count(DISTINCT([productName]))>2, '3 products',

if (Count(DISTINCT([productName]))>1, '2 products ', '1 product ')))

Any suggestions?

Re: Help with dimension in pivot table

Miguel_Angel_Baeyens — Fri, 26 Jul 2013 07:22:26 GMT

Hi,

Yes, if any, the following should do

=Aggr(If (Count(DISTINCT([productName]))>3, '4 products',
if (Count(DISTINCT([productName]))>2, '3  products',
if (Count(DISTINCT([productName]))>1, '2 products ', '1 product '))), Dimension2)

Anyway, I'd strongly recommend you to move that logic to the script, because calculated dimensions may have severe impact in your dashboard performance.

Hope that helps.

Miguel

Re: Help with dimension in pivot table

narender123 — Fri, 26 Jul 2013 07:23:33 GMT

Have you tried this in Add calculated dimention or in expression.

Try it there.

Re: Help with dimension in pivot table

Fri, 26 Jul 2013 07:30:02 GMT

Thanks, Miguel,

and how to move that logic to script?

Re: Help with dimension in pivot table

Fri, 26 Jul 2013 07:32:19 GMT

Thanks, Miguel,

and how to move that logic to script?

Re: Help with dimension in pivot table

Fri, 26 Jul 2013 07:38:43 GMT

Thanks, Miguel,

and how to move that logic to script?

Re: Help with dimension in pivot table

Miguel_Angel_Baeyens — Fri, 26 Jul 2013 07:42:32 GMT

Hey, easy with the reload button...

Moving that to the script might be as easy as the following

LOAD

If (Count(DISTINCT([productName]))>3, '4 products',
if (Count(DISTINCT([productName]))>2, '3 products',
if (Count(DISTINCT([productName]))>1, '2 products ', '1 product '))) AS Dimension

RESIDENT OriginalTable

GROUP BY SalesPerson;

Obviously, field names and table names are just an example, use your own.

Hope that helps.

Miguel

Re: Help with dimension in pivot table

Mon, 29 Jul 2013 09:45:28 GMT

Hey, Miguel,

One more question, I`ve loaded my data form txt file, where can i find the name of my table?

Re: Help with dimension in pivot table

Miguel_Angel_Baeyens — Mon, 29 Jul 2013 09:56:04 GMT

Hi,

Use the Table Viewer or Control + T when the document is open in the QlikView Desktop and you will see there your tables.

For what it's worth, get used to name your tables always in the load script, as follows:

Customers: // this is the table name
LOAD *
FROM Customers.qvd (qvd);

Hope that helps.

Miguel