Count distinct IDs

[AutoMann]

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

[sunny_talwar · Accepted solution]

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)

[Anil_Babu_Samineni]

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)