Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
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)