Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
swethasmilek
Contributor III
Contributor III

New to QlikView Stuck with Distinct

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.

AmountManagerGeo-codeOffice
500001020IND
510002020IND
211003010US
211002010BRA
500001010IND

Output Expected as

ABCDistinctDistinct 1
Amount32(500),2(211),1(510)
Manager32(001),2(002),1(003)
Geo-Code22(020),3(010)
Office33(IND),1(US),1(BRA)

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

1 Solution

Accepted Solutions
jagan
MVP & Luminary
MVP & Luminary

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.

View solution in original post

13 Replies
jagan
MVP & Luminary
MVP & Luminary

Hi,

Can you attach the sample file?

Regards,

jagan.

gupta_n8
Specialist II
Specialist II

PFA

Check the application

jagan
MVP & Luminary
MVP & Luminary

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.

View solution in original post

gupta_n8
Specialist II
Specialist II

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
swethasmilek
Contributor III
Contributor III

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

swethasmilek
Contributor III
Contributor III

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

jagan
MVP & Luminary
MVP & Luminary

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.

swethasmilek
Contributor III
Contributor III


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