topic Re: How to use distinct function . in QlikView

How to use distinct function .

narender123 — Thu, 05 Feb 2015 11:34:31 GMT

Hi All,

My current expression is :

=Count({<flag={1}>}distinct filer)

This distinct will work for monthly basis as my dimension is month wise.But I want this distinct function based on Yearly basis with the same monthly dimension.How i cant achieve this.

table:

Column No of Filer

-----------------------------------------

Jan2014 3

Feb2014 4

Mar2014 5

Apr2014 6

Dec2014 15

Thanks,

Narender

Re: How to use distinct function .

Anonymous — Thu, 05 Feb 2015 11:44:44 GMT

try to use aggr function and use year on aggr

Re: How to use distinct function .

narender123 — Thu, 05 Feb 2015 11:49:13 GMT

Pls tell me the format

Re: How to use distinct function .

Thu, 05 Feb 2015 11:54:44 GMT

Hi,

Please try the following

Count(TOTAL <Year> {<flag={1}>}distinct filer)

Regards,

Joshmi

Re: How to use distinct function .

narender123 — Thu, 05 Feb 2015 12:08:55 GMT

Hi,

Its showing data, total on each month is the same.

So not working.

Thanks

Re: How to use distinct function .

narender123 — Thu, 05 Feb 2015 12:21:55 GMT

Aggr is showing wrongly data as data for against all month is showing same . So total count on each month is same.

Re: How to use distinct function .

Peter_Cammaert — Thu, 05 Feb 2015 15:51:54 GMT

You can use an AsOf table here. Simple and powerful. There are other solutions if you could drop the DISTINCT clause...

See attachment.

Peter

Re: How to use distinct function .

narender123 — Fri, 06 Feb 2015 05:45:33 GMT

HI Pater,

where is the attachment??

Thanks.

Re: How to use distinct function .

narender123 — Fri, 06 Feb 2015 05:46:29 GMT

HI all,
Any one know how to sort out this???

Thanks

Re: How to use distinct function .

tresB — Fri, 06 Feb 2015 05:53:25 GMT

People might help sort this out faster if you could create a sample excel/qvw explaining expected output.

Re: How to use distinct function .

narender123 — Fri, 06 Feb 2015 06:05:21 GMT

Hi Tresco,

Thanks for the reply.

This time i dont have qlikview.As i am working on server directly.But i have shown the condition in my original query.

table:

Column No of Filer

-----------------------------------------

Jan2014 3

Feb2014 4

Mar2014 5

Apr2014 6

Dec2014 15

I am using this:

=Count({<flag={1}>}distinct filer)

Pls advice?

Re: How to use distinct function .

tresB — Fri, 06 Feb 2015 06:17:08 GMT

Sorry, expected output isn't yet crear to me.

Re: How to use distinct function .

Fri, 06 Feb 2015 06:29:31 GMT

Hi ,

Please create some sample dummy record set with expected output in excel. It will help us to understand actual requirement .

thanks

Re: How to use distinct function .

jonathandienst — Fri, 06 Feb 2015 07:16:07 GMT

I assume what you want to is count each Filer once. After it has been counted in one month, you no longer want to count it in later months. In other words, you want a count of the first time the Filer exists.

This is going to be tricky in an expression and if your data set is large, it may perform badly. So what I recommend is that a flag is created during load - the first time each Filer is encountered, the flag is set to 1. In later months, the flag is set to 0. Something like this (assuming you load the data in date order):

LOAD ....

date,

filer

If(Exists(filerID), 0, 1) As FirstInstanceFlag,

....

And then in your table, the expression becomes simply:

=Sum({<flag={1}>} FirstInstanceFlag)

HTH

Jonathan

Re: How to use distinct function .

Peter_Cammaert — Fri, 06 Feb 2015 16:00:09 GMT

Open the original discussion. Do not view my reply in your Inbox or on your phone. The attachment will only be visible/downloadable in the original discussion.