topic New to QlikView Stuck with Distinct in QlikView

New to QlikView Stuck with Distinct

Anonymous — Tue, 26 Aug 2014 03:47:31 GMT

Hi Team,Myself new to QlikView i'm facing a problem with Count Distinct in a POC. My problem is to find distinct columns in a Table along side need another column again disinct columns.I need Distinct columns at each individual level.

Amount	Manager	Geo-code	Office
500	001	020	IND
510	002	020	IND
211	003	010	US
211	002	010	BRA
500	001	010	IND

Output Expected as

ABC	Distinct	Distinct 1
Amount	3	2(500),2(211),1(510)
Manager	3	2(001),2(002),1(003)
Geo-Code	2	2(020),3(010)
Office	3	3(IND),1(US),1(BRA)

I got Distinct Number but failed to get Distinct 1 column results.Please do needful

Re: New to QlikView Stuck with Distinct

jagan — Tue, 26 Aug 2014 04:13:01 GMT

Hi,

Can you attach the sample file?

Regards,

jagan.

Re: New to QlikView Stuck with Distinct

Anonymous — Tue, 26 Aug 2014 05:27:25 GMT

PFA

Check the application

Re: New to QlikView Stuck with Distinct

jagan — Tue, 26 Aug 2014 05:58:32 GMT

Hi,

Try like this

Dimension:

Add Calculated Dimension: =ValueList('Amount', 'Manager', 'Geo-code', 'Office')

Expression:

Distinct

if(ValueList('Amount', 'Manager', 'Geo-code', 'Office')='Amount', Count(DISTINCT Amount),

if(ValueList('Amount', 'Manager', 'Geo-code', 'Office')='Geo-code', Count(DISTINCT [Geo-code]),

if(ValueList('Amount', 'Manager', 'Geo-code', 'Office')='Manager', Count(DISTINCT Manager),

if(ValueList('Amount', 'Manager', 'Geo-code', 'Office')='Office', Count(DISTINCT Office)))))

Distinct1

if(ValueList('Amount', 'Manager', 'Geo-code', 'Office') ='Manager', Concat(TOTAL Aggr( Count(Manager) & '(' & MaxString(Manager) & ')', Manager), ', '),

if(ValueList('Amount', 'Manager', 'Geo-code', 'Office') ='Amount', Concat(TOTAL Aggr( Count(Amount) & '(' & Amount & ')', Amount), ', '),

if(ValueList('Amount', 'Manager', 'Geo-code', 'Office') ='Geo-code',Concat(TOTAL Aggr( Count([Geo-code]) & '(' & [Geo-code] & ')', [Geo-code]), ', '),

if(ValueList('Amount', 'Manager', 'Geo-code', 'Office') ='Office', Concat(TOTAL Aggr( Count(Office) & '(' & Office & ')', Office), ', ')))))

Hope this helps you.

Regards,

Jagan.

Re: New to QlikView Stuck with Distinct

Anonymous — Tue, 26 Aug 2014 06:21:03 GMT

Check the ABC AUTOMATIC CHART.

Now no need to do any hardcoding....

Everything is handled in script.....

Re: New to QlikView Stuck with Distinct

jonathandienst — Tue, 26 Aug 2014 06:32:04 GMT

Another (possibly simpler) way without hard coding anything in script.Also sorts the elements in Distinct1.

HTH

Jonathan

Re: Re: New to QlikView Stuck with Distinct

Anonymous — Tue, 26 Aug 2014 08:31:06 GMT

I tried Approach didnt worked for me I am seding Sample data and POC purpose using QlikView Desktop .Please send in Non QVW Format

Re: Re: New to QlikView Stuck with Distinct

Anonymous — Tue, 26 Aug 2014 08:34:43 GMT

Can any one send script format not able to open in QVW.I attached Sample data.Thanx Jagan,Nitin,Jonathan for Quick response

Re: New to QlikView Stuck with Distinct

jagan — Tue, 26 Aug 2014 08:57:53 GMT

Hi Swetha,

The expressions in my previous post is working for me, it should also work in your dashboard. Can you share your sample Qlikview file that you are working, so that it would be easier to debug.

Regards,

Jagan.

Re: New to QlikView Stuck with Distinct

Anonymous — Tue, 26 Aug 2014 10:27:27 GMT

Thanx Jagan It works for me i missed some thing i finally fixed it.For me one doubt Is it Possible to create a comment tab, where the user can comment and information should be stored as history with data and username etc.,

Re: New to QlikView Stuck with Distinct

jagan — Tue, 26 Aug 2014 14:38:24 GMT

Hi,

If you are using Qlikview 11, Try Notes in Qlikview server.

From Qlikview 11 Reference Manual

Notes and Comments

The second new collaboration feature is Notes and Comments. This feature allows users to create and share

notes about QlikView objects. Notes are shared with other QlikView users and these others users can

respond by leaving their own comments. The users have the ability to save a snapshot (bookmark) of the

data with each Note and Comment.

Regards,

Jagan.

Re: New to QlikView Stuck with Distinct

Anonymous — Wed, 27 Aug 2014 09:07:23 GMT

One more Question in Attached ABC excel file i need a condition count of sum of premium_gross Values Adjacent to Distinct counts done Dimensios office, Geo,manager in a pivot Table.Any set analysis has to be written

Re: New to QlikView Stuck with Distinct

jagan — Wed, 27 Aug 2014 10:02:07 GMT

Hi,

Can you create another post so that it would be helpful for others too.

Regards,

Jagan.

Re: New to QlikView Stuck with Distinct

Anonymous — Wed, 27 Aug 2014 11:58:32 GMT

started new thread Jagan,can you please help me in Set analysis to diasplay gross http://community.qlik.com/thread/130962