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.
Kush,
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.
Settu,
How would we do the following in qlik?
if(subfield value like 'ABC*', subfield(value,1), subfield(value,2))
There is no LIKE - would we use wildmatch or match?
Thanks.
Then how you are going to decide that particular product is Service or Plan if order is not fixed?
Could you check the below attachment with your original data.?
then try this
Product:
LOAD [Phone Number],
if( Wildmatch(lower(Product),'*plan*'),Product) as Plan,
if(not Wildmatch(lower(Product),'*plan*'),Product) as Service
LOAD Product,
[Phone Number]
FROM table
where not wildmatch(lower(Product),'*other*');
Then perhaps this:
MapService:
Mapping LOAD [Phone Number],
Product
FROM table
where Product like 'ABC*';
MapPlan:
Mapping LOAD [Phone Number],
Product
FROM table
where Product like '*Plan';
Result:
LOAD
[Phone Number],
ApplyMap('MapService', [Phone Number]) as Service,
ApplyMap('MapPlan', [Phone Number]) as Plan
FROM table;
Oh, and there is a Like command in QV as used above. Or use WildMatch(...) if you prefer.
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],
If(Wildmatch(Product,'*ABC*'),Product) as Product,
If(Wildmatch(Product,'*Plan*'), Product) as Service
Resident T1 where Wildmatch(Product,'*ABC*','*Plan*') order by Product;
NoConcatenate
T3:
Load [Phone Number],
Concat(Product,'') as Product ,
Concat(Service,'') as Service
Resident T2 Group by [Phone Number];
DROP Table T1, T2;