topic Re: Distinct values in QlikView

Distinct values

Fri, 11 Apr 2014 08:55:45 GMT

Hi,

Could you please resolve the issue

Re: Distinct values

MK_QSL — Fri, 11 Apr 2014 08:58:33 GMT

What is the Issue?

Re: Distinct values

er_mohit — Fri, 11 Apr 2014 09:04:11 GMT

if there's duplicate records in table file

the use

load distinct fieldname

from your data source;

or for expression side

write in text box

= count(distinct duplicate record fieldname) //duplicate record fieldname is your fieldname

Re: Distinct values

its_anandrjs — Fri, 11 Apr 2014 09:06:31 GMT

Please clear the requirement.

if you want to know about DISTINCT

Eg:-

100

200

300

100

400

200

If you count this ID with the below script in any text box

Count(ID) = you get 7

and with DISTINCT see the result

Count(DISTINCT ID) = you get 4 which is unique.

Re: Distinct values

Fri, 11 Apr 2014 09:07:51 GMT

I have three tables ,

Date Flightno Service used

and in service used there are two type of service So on a same date one Flight can used any number of services but i want to count as a one but another day same flight can avail the service but i want to count its another day service as one too. so its like for a month i want count total no of distinct flights used the services.

Re: Distinct values

MK_QSL — Fri, 11 Apr 2014 09:26:30 GMT

Create a straight Table

Dimensions

Month

Service

Expression

Count(Distinct Flight)

Re: Distinct values

its_anandrjs — Fri, 11 Apr 2014 09:29:52 GMT

Create a composite key between fields like

Date&'-'&Flightno as DateAndFilghtNo

Date&'-'&Flightno&'-'&[Service used] as DateAndFilghtNoWithservice

and count those keys with DISTINCT or direct count

if you have sample then provide please.

Re: Distinct values

Fri, 11 Apr 2014 10:02:37 GMT

date	flightno	service used
20131022005811	B	PCA
20131022005811	B	PCA
20131022005611	C	GPU
20131022005811_	D	GPU
20131022005812	A	PCA
20131021005811_	B	PCA
20131021005811_	A	PCA
20131023005811_	V	GPU
20131023005811_	B	GPU
20131023005811_	B	GPU
2013102300511	C	GPU
20131023005811_	D	GPU
20131024005811_	A	GPU
20131024005811_	B	GPU
20131024005811_	A	GPU

I want to calculate % total no of flight used Gpu = No of flight used GPU/Total no of Flights

condition is that any of entries of same flight on a same day should be count as 1 .

Re: Distinct values

Fri, 11 Apr 2014 10:11:49 GMT

you can get the total number of flights as COUNT(DISTINCT flightNo)

according to your example Total number of flights would be 5

Re: Distinct values

MK_QSL — Fri, 11 Apr 2014 10:14:21 GMT

Create a straight table

Dimension = Date or Month

Expression

COUNT({<[service used] = {'GPU'}>}flightno)/Count(TOTAL flightno)

COUNT({<[service used] = {'GPU'}>}flightno)/Count(TOTAL DISTINCT flightno)

Re: Distinct values

its_anandrjs — Fri, 11 Apr 2014 10:39:17 GMT

You can

=Count(DISTINCT flightNo)

=Count(flightNo)

Re: Distinct values

Fri, 11 Apr 2014 10:54:52 GMT

i cant use count( distinct flightno)

this will count distinct flights of whole month.

but i want that it should calculate distinct flights of day wise not month wise and i want to calculate monthly data.

Re: Distinct values

MK_QSL — Fri, 11 Apr 2014 10:56:24 GMT

COUNT({<[service used] = {'GPU'}>}flightno)/Count(TOTAL flightno)

Re: Distinct values

its_anandrjs — Fri, 11 Apr 2014 11:00:47 GMT

Use pivot table and use this expression for the calculation

=Count(DISTINCT flightNo)

=Count(flightNo)

Re: Distinct values

Fri, 11 Apr 2014 11:06:56 GMT

according to my table on 22nd flight B has used two service but i want to calculate it as one but on 23 rd b has again used two service so i want to count it as 1 and on 24th b has used 1 service so i count as one so total count should calculate like this . like wise for every flight ..

Re: Distinct values

MK_QSL — Fri, 11 Apr 2014 11:51:21 GMT

You are mentioning dates which you haven't given in your sample data.

Can you provide some more data please?

Re: Distinct values

jonathandienst — Fri, 11 Apr 2014 12:12:08 GMT

Assuming you have a table or chart with a month field (month) as a dimension:-

=Sum(Aggr(Count(Distinct flightno), date, month))

This will count distinct flights on each day in the month.

HTH

Jonathan

Re: Distinct values

Mon, 14 Apr 2014 04:29:42 GMT

Thanks ]