Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I'm trying to retrieve a list of rows where Costs per Month per Agent is duplicate.
I have my data setup as follows:
Salesman | Product | Cost | Month | RptType | Agent |
Leroy | Car | 160 | 4 | General | USA |
Richard | Car | 2000 | 3 | General | France |
Samuel | Plane | 2000 | 3 | General | France |
Leroy | Plane | 15000 | 2 | General | USA |
Samuel | Car | 300 | 2 | General | USA |
Victor | Car | 300 | 2 | General | USA |
Richard | Bus | 500 | 1 | General | USA |
Johan | Car | 500 | 1 | General | USA |
Johan | Bus | 700 | 1 | General | USA |
Leroy | Boat | 800 | 1 | General | USA |
David | Plane | 900 | 5 | General | USA |
I've setup the Dimensions and Expressions as follows
Dimensions:
1. Aggr(if(Month>=1 and Month<=4 and RptType = 'General' and Count(Month&'_'&Cost)>=2,Cost),Cost)
2. Salesman
3. Month
Expressions:
1. Agent
2. count(aggr(count(Month&'_'&'_'&Agent&'_'&Cost),Salesman))
This is currently what I have, which is incorrect
Cost | Month | Agent | Salesman | #Rows with same Cost+Month+Agents |
500 | 1 | USA | Richard | 1 |
500 | 1 | USA | Johan | 1 |
300 | 2 | USA | Samuel | 1 |
300 | 2 | USA | Victor | 1 |
2000 | 3 | France | Richard | 0 |
2000 | 3 | France | Samuel | 0 |
This is what I expect
Cost | Month | Agent | Salesman | #Rows with same Cost+Month+Agents |
500 | 1 | USA | Richard | 2 |
500 | 1 | USA | Johan | 2 |
300 | 2 | USA | Samuel | 2 |
300 | 2 | USA | Victor | 2 |
2000 | 3 | France | Richard | 2 |
2000 | 3 | France | Samuel | 2 |
I want to highlight rows by counting the duplicate Month+Cost+Agent via expression 2, but I'm not sure what it should be.
Can someone pls advise what should I be using? Thanks!
What about an expression like
Sum(TOTAL<Month,Agent,Cost>
Aggr(Count(Month&Agent& Cost)
, Month, Agent, Cost)
)
Or a script solution:
TAB1:
LOAD * INLINE [
Salesman Product Cost Month RptType Agent
Leroy Car 160 4 General USA
Richard Car 2000 3 General France
Samuel Plane 2000 3 General France
Leroy Plane 15000 2 General USA
Samuel Car 300 2 General USA
Victor Car 300 2 General USA
Richard Bus 500 1 General USA
Johan Car 500 1 General USA
Johan Bus 700 1 General USA
Leroy Boat 800 1 General USA
David Plane 900 5 General USA
] (delimiter is '\t');
Left JOIN
LOAD Month, Agent, Cost,
Count(Month&Agent&Cost) as Count
RESIDENT TAB1
GROUP BY Month, Agent, Cost;
From your sample data, I see none of the rows to have any duplicates when I see Cost+Month+Agent? Is this data incomplete or am I missing something?
Hi Sunny, Thanks for your reply.
Pls allow me to clarify by my meaning of "duplicate". I was trying to say that some rows showed up with the same values of Cost,Month and Agent... and I need to highlight these rows if the Cost, Month and Agent for the current row's Salesman is NOT unique i.e. count > =1
After applying the dimensions/expressions stated on my sample data, I was able to generate this straight-table chart:
Cost | Month | Agent | Salesman | #Rows with same Cost+Month+Agents |
500 | 1 | USA | Richard | 1 |
500 | 1 | USA | Johan | 1 |
300 | 2 | USA | Samuel | 1 |
300 | 2 | USA | Victor | 1 |
2000 | 3 | France | Richard | 0 |
2000 | 3 | France | Samuel | 0 |
As you can see, columns Cost,Month and Agent are duplicated 2x for both Richard and Johnson: 500 , 1, USA
Similarly, for Samuel and Victor, they both have 300,2,USA and Richard and Samuel has 2000,3,France
Hope that helps to clarify what I meant by "duplicates". Thanks.
What about an expression like
Sum(TOTAL<Month,Agent,Cost>
Aggr(Count(Month&Agent& Cost)
, Month, Agent, Cost)
)
Or a script solution:
TAB1:
LOAD * INLINE [
Salesman Product Cost Month RptType Agent
Leroy Car 160 4 General USA
Richard Car 2000 3 General France
Samuel Plane 2000 3 General France
Leroy Plane 15000 2 General USA
Samuel Car 300 2 General USA
Victor Car 300 2 General USA
Richard Bus 500 1 General USA
Johan Car 500 1 General USA
Johan Bus 700 1 General USA
Leroy Boat 800 1 General USA
David Plane 900 5 General USA
] (delimiter is '\t');
Left JOIN
LOAD Month, Agent, Cost,
Count(Month&Agent&Cost) as Count
RESIDENT TAB1
GROUP BY Month, Agent, Cost;
Hi Stefan,
Thanks! I was examining my app and wondering how to implement it as my data model had changed with some conditions and I had to add in more calculated dimensions than before...
Nevertheless, got it to work using your solution!