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.

[sjhussain]

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.

[sjhussain]

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.

[Kushal_Chawda]

Then how you are going to decide that particular product is Service or Plan if order is not fixed?

[tamilarasu]

Could you check the below attachment with your original data.?

[Kushal_Chawda]

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*');

[jonathandienst]

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.

[tamilarasu]

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;