topic Re: Count distinct is not working in script? in QlikView

Count distinct is not working in script?

Tue, 10 Dec 2013 10:51:53 GMT

Dear community,

I found problem for script below, the FlightNumber is always showing 1...

FlightNumber:

Load Fltno,

[Flt Dt],

Count(distinct Fltno) * Count(distinct [Flt Dt])) as FlightNumber

resident FlightData

group by [Flt Dt], Fltno;

I think count distinct might not work in this way in script, anyone knows to workaround to overcome this?

it is working fine in expression in chart.

For special reason, i cannot have expression in the chart, i need a (number of flight) field,

so that the chart can directly use the field.

(limitation of GeoQlik flow chart, only can use field, expression is not working)

I need the number of flight field can corresponding to the filter selection. just like the figure show in blue text box.

Thanks and best regards,

Chanel

Re: Count distinct is not working in script?

manideep78 — Tue, 10 Dec 2013 11:01:33 GMT

What exact Calculation do you want over there?

You are taking distinct Fltno and [Flt Dt] for each record. So they both always give 1 and hence 1*1=1.

Re: Count distinct is not working in script?

jonathandienst — Tue, 10 Dec 2013 11:03:11 GMT

Since you are grouping by Fltno and Flt Dt, the distinct counts will always be 1, so the count distinct is working correctly. What are you trying to do? Perhaps you should explain how you would like to determine FlightNumber.

Regards

Jonathan

Re: Count distinct is not working in script?

Tue, 10 Dec 2013 11:11:51 GMT

It seems not a calculation issue.

Better you put some of your sample data and sample output you want from that, it'll describe the problem in a better way.

Re: Count distinct is not working in script?

Wed, 11 Dec 2013 03:53:57 GMT

Manideep HV, Jonathan Delpino Awlad Hossain, I have attached the example document with explanation why I need to in this way.

Kindly assist.

Thanks and best regards,

Chanel

Re: Count distinct is not working in script?

jagan — Wed, 11 Dec 2013 04:04:25 GMT

Hi,

Try this script and check

Data:

LOAD Cabin,

DOW,

[Flt Wk Dt],

[Flt Dt],

Fltno,

Region,

Route,

DepIATA,

DestIATA,

DestIATA2,

Origin,

Destination,

ASK,

RPK

FROM

[..\1_Resources\Map\sampledata for QC troubleshooting.xlsx]

(ooxml, embedded labels, table is Sheet2);

FlightNumber:

Load Fltno,

//count(Fltno) as FlightNumber

(Count(distinct Fltno) * Count(distinct [Flt Dt])) as FlightNumber

resident Data

group by Fltno;

Regards,

Jagan.

Re: Re: Count distinct is not working in script?

Wed, 11 Dec 2013 05:09:05 GMT

Hi Jagan,

Thanks for fast response, one step closer now. but it still looks not working correctly as shown in attachment.

We should get 112 flights for KUL-SIN route, but we can see 12 and 14 in [number of flight] field.

Thanks and best regards,

Chanel

Re: Re: Count distinct is not working in script?

jagan — Wed, 11 Dec 2013 05:16:05 GMT

Hi,

Can you attach some sample data?

Regards,

Jagan.

Re: Re: Count distinct is not working in script?

Wed, 11 Dec 2013 05:24:07 GMT

Hi Jagan,

I have attached the sample data in last reply named as sampledata for QC troubleshooting.xlsx

Thanks and best regards,

chanel

Re: Count distinct is not working in script?

jagan — Wed, 11 Dec 2013 05:38:28 GMT

Hi,

We are doing Group by Flight Number, so you are getting 12 and 14, if you want 112 then you need do Group by using Route dim.

FlightNumber:

Load Route,

//[Flt Dt],

//count(Fltno) as FlightNumber

(Count(distinct Fltno) * Count(distinct [Flt Dt])) as FlightNumber

resident Data

group by Route;

Regards,

Jagan.

Re: Re: Count distinct is not working in script?

Wed, 11 Dec 2013 06:56:11 GMT

Hi Jagan,

Thanks for this hint, i should group by route and [Flt Wk Dt].

But I just realized my expression to calculate number of flights is incorrect.

As shown in attachment, for ADL-KUL route, there should be 8 flights but my expression result is 14 flights.

= count (distinct Fltno) * count (distinct [Flt Dt]) is not the correct formula...

Appreciate if you could edit my expression.

Thanks and best regards,

Chanel

Re: Count distinct is not working in script?

jagan — Wed, 11 Dec 2013 07:08:57 GMT

Hi,

Try any of this expressions

=Sum(Aggr(1, Fltno, [Flt Dt]))

= count(DISTINCT Fltno & Ceil([Flt Dt]))

Regards,

Jagan.

Re: Count distinct is not working in script?

Wed, 11 Dec 2013 09:45:47 GMT

Awesome Jagan! Thanks Guru!