topic Re: Group by Column and then add another Field in QlikView

Group by Column and then add another Field

sjhussain — Mon, 04 Apr 2016 04:50:17 GMT

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.

Re: Group by Column and then add another Field

settu_periasamy — Mon, 04 Apr 2016 04:54:05 GMT

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;

Re: Group by Column and then add another Field

jonathandienst — Mon, 04 Apr 2016 05:08:28 GMT

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

Re: Group by Column and then add another Field

sjhussain — Mon, 04 Apr 2016 05:12:44 GMT

Jonathan,

Thanks for the response,

* Data is not sorted

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

Thanks.

Re: Group by Column and then add another Field

sjhussain — Mon, 04 Apr 2016 05:18:45 GMT

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.

Re: Group by Column and then add another Field

sjhussain — Mon, 04 Apr 2016 05:20:46 GMT

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..

Re: Group by Column and then add another Field

settu_periasamy — Mon, 04 Apr 2016 05:35:00 GMT

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..

Re: Group by Column and then add another Field

jonathandienst — Mon, 04 Apr 2016 05:38:19 GMT

>>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?

Re: Group by Column and then add another Field

Kushal_Chawda — Mon, 04 Apr 2016 05:39:04 GMT

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];

Re: Group by Column and then add another Field

sjhussain — Mon, 04 Apr 2016 05:50:18 GMT

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.

Re: Group by Column and then add another Field

sjhussain — Mon, 04 Apr 2016 05:50:41 GMT

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.

Re: Group by Column and then add another Field

sjhussain — Mon, 04 Apr 2016 05:52:31 GMT

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.

Re: Group by Column and then add another Field

Kushal_Chawda — Mon, 04 Apr 2016 05:58:16 GMT

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

Re: Group by Column and then add another Field

tamilarasu — Mon, 04 Apr 2016 05:58:37 GMT

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

Re: Group by Column and then add another Field

Kushal_Chawda — Mon, 04 Apr 2016 06:01:52 GMT

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

Re: Group by Column and then add another Field

jonathandienst — Mon, 04 Apr 2016 06:26:13 GMT

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.

Re: Group by Column and then add another Field

tamilarasu — Mon, 04 Apr 2016 06:30:36 GMT

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;