topic Re: How to make Unique Number? in QlikView

How to make Unique Number?

Tue, 05 May 2015 07:05:22 GMT

Hi Experts,

i have a request that i have to get the Unique Numbers from given numbers

Sample Given Table:

Id	Months	Names	Visits
0021	JAN	Jack	340
9045	JAN	John	248
4561	JAN	Leslie	3

OUTPUT:

Months	Header 3	Visits	Unique Visits
JAN	Jack	340	1
JAN	John	248	1
JAN	Leslie	3	1

So each Month Unique Visits Increments by 1

E.g JAN = 1

FEB = 1

MAR = 1

APR = 1

and so on, so in a year it should be 12 Visits.

Re: How to make Unique Number?

petter — Tue, 05 May 2015 07:08:14 GMT

AutoNumber is an option

Re: How to make Unique Number?

Tue, 05 May 2015 07:15:10 GMT

Hi,

How can i Utilize it on an Expression. Straight or Pivot Table?

Please supply an Example.

Re: How to make Unique Number?

Peter_Cammaert — Tue, 05 May 2015 07:19:24 GMT

How about =count(DISTINCT Names)?

Re: How to make Unique Number?

Tue, 05 May 2015 07:22:46 GMT

Hi,

Below is my Expression Used:

=sum(aggr(count(DISTINCT SalesDate),CustomerName,SalesDate))

Please Help.

Re: How to make Unique Number?

Anonymous — Tue, 05 May 2015 07:35:40 GMT

use in script autonumberhash256(Id,Months,Year) as uniqueId

If it is for only one year need not consider year but if you need for future many years, you need to consider year also.

at front end table ---> count(uniqueId)

Re: How to make Unique Number?

PrashantSangle — Tue, 05 May 2015 07:48:58 GMT

How do you calculate Visits????

sum(aggr(count(DISTINCT SalesDate),CustomerName,Months,Year))

Regards

Re: How to make Unique Number?

Anonymous — Tue, 05 May 2015 07:53:15 GMT

what unique number you want to show?

Re: How to make Unique Number?

Tue, 05 May 2015 07:58:24 GMT

Hi,

Below should be output:

id	Total Visits	Unique Visits
0023	12	1
454	3	1
887	37	1
069	244	1
009	78	1

For each id i must get unique number regardless how many visits i made in a Month

Below is the Expression i use to get total visits made:

=sum(aggr(count(DISTINCT SalesDate),CustomerCodeName,SalesDate))

Re: How to make Unique Number?

Peter_Cammaert — Tue, 05 May 2015 08:07:40 GMT

So for every Id unique visits is ... 1. Otherwise the Id line would not appear in your table.

Re: How to make Unique Number?

Peter_Cammaert — Tue, 05 May 2015 08:10:49 GMT

I think I get it. You want the number of unique visits per month for all selected months?

Then how about:

=sum(aggr(count(DISTINCT CustomerCodeName), SalesMonth))

Re: How to make Unique Number?

Tue, 05 May 2015 08:11:42 GMT

Yes,

How can i Tackle it, so that every month is equal to 1 if there's Visit if not make it Zero.

Regards.

Re: How to make Unique Number?

Peter_Cammaert — Tue, 05 May 2015 08:11:59 GMT

Note that when you add dimensions to your straight table, you may have to add them to the aggr() function as well.

Re: How to make Unique Number?

Peter_Cammaert — Tue, 05 May 2015 08:15:21 GMT

That depends on whether or not Month is a dimension in your table.

My first suggestion =count(DISTINCT Names) would serve you well in a table with just Month as a dimension. The second one should do in any other table.

Re: How to make Unique Number?

Tue, 05 May 2015 08:39:08 GMT

Hi,

below is sample answer i got:

Month	SalesRepID	Distinct Reps	Distinct SalesDate	Total Visits	Distinct Unique Visits
Apr	0032	1	1	12	1
Apr	0043	1	1	8	0
Apr	0082		1	13	-
Apr	0065		1	13	-

On Distinct Unique Visits it should be only 1 for all Rows.

I can't get this right, its giving me a headache.

=aggr(Count(DISTINCT SalesDate),SalesDate)

Re: How to make Unique Number?

petter — Tue, 05 May 2015 09:11:23 GMT

AutoNumber is a function that is available only in a load script. So you could pre-calculate it in a Load Script.

If you want it dynamically assigned you will have to do Count(DISTINCT Id) in a chart:

Dimensions: Id, Months, Names

Expressions: Sum(Visits), Count(DISTINCT Id)

For the Count(DISTINCT Id) in a Straight Table you will have to on the Expressions Tab select Sum of Rows for Total Mode for that particular expression.

In a Pivot Table you will have to have a slightly different expression for the last expression:

If( RowNo() = 0 , Count(NoOfRows()) - 1 , Count(DISTINCT Id) )

Re: How to make Unique Number?

petter — Tue, 05 May 2015 22:01:21 GMT