New to QlikView Stuck with Distinct

[Anonymous]

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

[jagan]

Hi,

Can you attach the sample file?

Regards,

jagan.

[Anonymous]

PFA

Check the application

[jagan · Accepted solution]

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.

[Anonymous]

Check the ABC AUTOMATIC CHART.

Now no need to do any hardcoding....

Everything is handled in script.....

[jonathandienst]

Hi

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

HTH

Jonathan

[Anonymous]

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

[Anonymous]

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

[jagan]

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.

[Anonymous]

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.,