Creating New Table in Script Editor

[labo2002]

Hello all,

To simplify my question, i have a table with CustID and Activity.  The goal is to create a new customer table wherein it will categorize the Cust by how many activities they did. We are trying to group the customer based on the frequency of their activities. 

Existing Table:

CustID	Activity
CustA	1
CustA	2
CustB	3
CustA	4
CustC	5
CustC	6
CustA	7
CustB	8
CustD	9
CustE	10
CustA	11

Result

CustID	No.of Activity
CustA	5 or more
CustB	2 times
CustC	2 times
CustD	1 time
CustE	1 time

Appreciate your help guys..

[Taoufiq_Zarra]

Hi,

One solution if you want to keep only the results table :

Map:
Mapping load * inline
[
Ccode,Value
1,1 time
2,2 times
3,3 times
4,4 times
5,5 or more
];

table1:
LOAD * INLINE [
    CustID, Activity
    CustA, 1
    CustA, 2
    CustB, 3
    CustA, 4
    CustC, 5
    CustC, 6
    CustA, 7
    CustB, 8
    CustD, 9
    CustE, 10
    CustA, 11
];

Table2:
noconcatenate

load CustID,Applymap('Map',count(Activity),'5 or more') as [No.of Activity] resident table1 group by CustID;


drop table table1;

output :

[Taoufiq_Zarra]

or left join to keep table 1 field

Map:
Mapping load * inline
[
Ccode,Value
1,1 time
2,2 times
3,3 times
4,4 times
5,5 or more
];

table1:
LOAD * INLINE [
    CustID, Activity
    CustA, 1
    CustA, 2
    CustB, 3
    CustA, 4
    CustC, 5
    CustC, 6
    CustA, 7
    CustB, 8
    CustD, 9
    CustE, 10
    CustA, 11
];
left join

load CustID,Applymap('Map',count(Activity),'5 or more') as [No.of Activity] resident table1 group by CustID;

[tresesco]

By counting you could do it easily in UI as well, however since you are looking for a separate table try like:

table1:
LOAD * INLINE [
    CustID, Activity
    CustA, 1
    CustA, 2
    CustB, 3
    CustA, 4
    CustC, 5
    CustC, 6
    CustA, 7
    CustB, 8
    CustD, 9
    CustE, 10
    CustA, 11
];

table2:
Load
	CustID,
	If(Count(Activity)>=5, '5 or More', Count(Activity))&' times' as [No. Of Acivity]
Resident table1 Group By CustID;	

[NitinK7]

Hi,

try below

ABC:
Load CustID,
if(count(CustID)>=5,'5 or more Times',
if(Count(CustID)=4,'4 Times',
if(Count(CustID)=3,'3 Times',
if(Count(CustID)=2,'2 Times',
if(count(CustID)=1,'1 Times'))))) as [No.of Activity]
Group By CustID;

load * Inline [
CustID, Activity
CustA, 1
CustA, 2
CustB, 3
CustA, 4
CustC, 5
CustC, 6
CustA, 7
CustB, 8
CustD, 9
CustE, 10
CustA, 11
];

[Saravanan_Desingh]

One solution is.

tab1:
LOAD * INLINE [
    CustID, Activity
    CustA, 1
    CustA, 2
    CustB, 3
    CustA, 4
    CustC, 5
    CustC, 6
    CustA, 7
    CustB, 8
    CustD, 9
    CustE, 10
    CustA, 11
];
Left Join(tab1)
LOAD CustID, Cnt&if(Cnt>=5,' or more',' time')&if(Cnt>1,'s') As [No.of Activity];
LOAD CustID, Count(CustID) As Cnt
Resident tab1
Group By CustID;

[Saravanan_Desingh]

Output: