Group by Column and then add another Field

[sjhussain]

Dear All,

We have a Phone number field and associated Products list. What we want to do is that for each number we want to define two columns - Service and Plan

Below is the sample data:

Product,Phone Number

ABC Enterprise,10001

Fleet Control  Premium Plan,10001

Other1, 10001

Other2, 10001

ABC Enterprise,20002

Fleet Control  Basic Plan ,20002

Other1, 20002

Other2, 20002

ABC Personal,30003

Other1, 30003

Other2, 30003

Fleet Control Basic Plan ,30003

We want to have a single row for each Phone Number along with the Service and the Plan.

Result:

Phone Number, Service, Plan

10001, ABC Enterprise, Fleet Control Premium Plan

20002, ABC Enterprise, Fleet Control Basic Plan

30003, ABC Personal, Fleet Control Basic Plan

Note: The products can appear in any order - it is not necessary that it will be the 1st or 2nd which have the product and the service.

Will appreciate assistance.

Thanks.

[settu_periasamy]

Try this..

T1:
LOAD * INLINE [
Product, Phone Number
ABC Enterprise, 10001
Fleet Control  Premium Plan, 10001
Other1, 10001
Other2, 10001
ABC Enterprise, 20002
Fleet Control  Basic Plan , 20002
Other1, 20002
Other2, 20002
ABC Personal, 30003
Other1, 30003
Other2, 30003
Fleet Control Basic Plan , 30003
];

NoConcatenate
T2:
LOAD [Phone Number],
subfield(Concat(Product,','),',',1) as Service,
subfield(Concat(Product,','),',',2) as Plan Resident T1 Group by [Phone Number];

DROP Table T1;

[jonathandienst]

Is the data sorted? What do you want to do with the 'Otherx" lines?

[sjhussain]

Jonathan,

Thanks for the response,

* Data is not sorted

* At the moment we do not want to do anything with the "Others" lines

Thanks.

[sjhussain]

Dear Settu,

Thanks for the response.

LOAD [Phone Number],
subfield(Concat(Product,','),',',1) as Service,
subfield(Concat(Product,','),',',2) as Plan Resident T1 Group by [Phone Number];

What if the Service and Plan are not the 1st and 2nd field?

Can we do a applymap?  or search by "ABC*" for Service and the "* Plan" for plan?

Thanks.

[sjhussain]

Also the data would be huge and we are reading from multiple excel sheets.  Do you think concat in the subfield would still give the performance? on huge data?

Thanks..

[settu_periasamy]

Hi Syed,

Just i have provided the suggestion based on the provided data. Yes we can achieve it in several ways.

if we do search (e.g ABC* and *Plan), we need to do one by one row, then Left join.

What if the Service and Plan are not the 1st and 2nd field?

Here, we can do the search like if(subfield value like 'ABC*', subfield(value,1), subfield(value,2))

Can we do a applymap?  or search by "ABC*" for Service and the "* Plan" for plan?

Here, Again, we need to find the distinct values for PhoneNumber,  Service / Phone Number, Plan  (two tables)

and apply the result..

[jonathandienst]

>>Data is not sorted.

How do you decide which line contains the Service and which lines contain the Plan?

How do you know whether to include a line?

Its easy to exclude 'Other*', but are all the 'others' defined like that?

[Kushal_Chawda]

try this

Product:

LOAD Product,

             [Phone Number]

FROM table

where not wildmatch(lower(Product),'*other*');

New:

LOAD [Phone Number],

if(Flag=1,Product) as Service,

if(Flag=2,Product) as Plan;

LOAD *,

            if(rowno()=1 or Product <> previous(Product),1, peek('Flag')+1) as Flag

Resident Product

Order by [Phone Number];

[sjhussain]

Jonathan,

The Service is two type "ABC Enterprise and ABC Personal",  The plan would have the word "Plan" in it.

The products can appear in any order - it is not necessary that it will be the 1st or 2nd which have the product and the service.

Hope this helps.

Thanks.