topic Re: Count distinct IDs in QlikView

Count distinct IDs

AutoMann — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I love this forum :), got so much help already:

Question:

I have a table with Customer ID and Order Priority.

I want to count how many customers have Priority1 Orders, how many customers Priority2 ...

But, if customer1 already was counted as a Priority1 Order customer, I do not want to include this customer in the Priority2 count.

CustomerID	PriorityOrder	Result
1	PRIO1	x
1	PRIO1
2	PRIO1	x
2	PRIO2
3	PRIO2	x
3	PRIO3

Result: 2 Customers with Prio1 Order, 1 Customer with Prio2 Order

I can either modify the load or the expression if required.

Thanks for your help

Re: Count distinct IDs

sunny_talwar — Fri, 19 Jul 2019 13:37:12 GMT

May be create a first time flag in the script like this

Table: LOAD * INLINE [ CustomerID, PriorityOrder 1, PRIO1 1, PRIO1 2, PRIO1 2, PRIO2 3, PRIO2 3, PRIO3 ]; FinalTable: LOAD *, If(CustomerID <> Previous(CustomerID), 1, 0) as FirstTimeFlag Resident Table Order By CustomerID, PriorityOrder; DROP Table Table;

and then use this in your expression

Dimension

PriorityOrder

Expression

Sum(FirstTimeFlag)

Re: Count distinct IDs

Anil_Babu_Samineni — Fri, 19 Jul 2019 13:55:53 GMT

Even this works in script

LOAD *, If(CustomerID<>Peek(CustomerID),1) as Flag Inline [
CustomerID, PriorityOrder
1, PRIO1
1, PRIO1
2, PRIO1
2, PRIO2
3, PRIO2
3, PRIO3
];

and KPI's are

Prior 1: Sum({<PriorityOrder={'PRIO1'}>} Flag)

Prior 2: Sum({<PriorityOrder={'PRIO2'}>} Flag)