Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a table like below. "Decp1" is repeated 5 time, but I want unique count on its "Number". If I remove duplicates in number then I get 3 & I need this as a flag in script but not 5.
Number | Text | Dup |
1003 | Decp1 | 3 |
1003 | Decp1 | 3 |
1003 | Decp9 | 2 |
1004 | Decp1 | 3 |
1004 | Decp1 | 3 |
1005 | Decp1 | 3 |
1005 | Decp7 | 0 |
1005 | Decp8 | 0 |
1005 | Decp9 | 2 |
make resident load Max(dup) group by
There are many ways to do this. You can do an autonumber of the two fields, concatenated, or you can perform the distinct sum of the fields, concatenated.
Examples:
Tab:
LOAD *, AutoNumber(Number&'|'&Text) as Dist Inline
[
Number,Text
1003,Decp1
1003,Decp1
1003,Decp9
1004,Decp1
1004,Decp1
1005,Decp1
1005,Decp7
1005,Decp8
1005,Decp9
];
Tab:
LOAD distinct * Inline
[
Number,Text
1003,Decp1
1003,Decp1
1003,Decp9
1004,Decp1
1004,Decp1
1005,Decp1
1005,Decp7
1005,Decp8
1005,Decp9
];
Count(Distinct Number&'|'&Text)
Hi Check out using this code below,
Data: LOAD * , Count( Distinct Number&'|'&Text) as Temp_Count INLINE [ Number, Text, Dup 1003, Decp1, 3 1003, Decp1, 3 1003, Decp9, 2 1004, Decp1, 3 1004, Decp1, 3 1005, Decp1, 3 1005, Decp7, 0 1005, Decp8, 0 1005, Decp9, 2 ]Group By Number,Text,Dup; join load Text, Count(Temp_Count) as Final_Count Resident Data Group by Text ; Drop Field Temp_Count;
PFA For ref.