Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Is the data sorted? What do you want to do with the 'Otherx" lines?
Jonathan,
Thanks for the response,
* Data is not sorted
* At the moment we do not want to do anything with the "Others" lines
Thanks.
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.
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..
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..
>>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?
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];
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.