Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
prma7799
Master III
Master III

Data grouping

Hi All,

I have one field called customer in that have many records , So I want to do grouping on that same records.

Please see below image I have customer name base on customer name i want Customer Grouping field as like below

Ex Output .

group.png

1 Solution

Accepted Solutions
settu_periasamy
Master III
Master III

Check the Attachment..

View solution in original post

13 Replies
Anonymous
Not applicable

I would suggest - Use Pivot table and make Customer grouping as first column then use CustmerName.

Thanks

BKC

settu_periasamy
Master III
Master III

As per my understanding, you can create one excel file with you Customer Group Name. And you can create the Field based on your Customer Name using Mapping and ApplyMap Functions..

Like.

T1:

Mapping

LOAD * Inline [

    CustomerName, Customer Grouping

    ABEX Pharama, ABEX Pharama

    ABKarr, Accord

    Accord Healthcar Ltd, Accord

    Accord SA, Accord

    Actavis ABC, Actavis

    Actavis US, Actavis

];

LOAD ApplyMap('T1',CustomerName,'Unknown') as CustomerGrouping

robert_mika
Master III
Master III

Is the field Grouping already in your data set or you need to create it?

If yes what's are the criteria for each of the group?

(especially for the second one? How do we know that Watson = Activis)

Feeling Qlikngry?

How To /Missing Manual(25 articles)

Anonymous
Not applicable

I would suggest 2 way to do this:

1) Do the hardcoding at script level sth like:

IF(CustomerName='ABEX Pharama(PTY) Ltd','ABEX Pharama(PTY) Ltd',

IF(CustomerName='ABKARR CORPORATION','ABKARR CORPORATION',

IF(CustomerName='Accord Healthcare Ltd' or CustomerName='Accord Healthcare SA','Accord Healthcare Ltd'

and so on //Rename it as Customer Grouping


2)

Create a Inline Function or Excel Sheet for same like:


load * inline

[Customer ID, CustomerName, CustomerGrouping

111, ABEX Pharama(PTY) Ltd, ABEX Pharama(PTY) Ltd,

112, ABKARR CORPORATION,ABKARR CORPORATION,

and so on

];

Then link this Inline table or excel file with ID in data model!!


Hope this help!

Kushal_Chawda

create the Grouping in the script as below

=if(wildmatch(lower(CustomerName),'accord*'),'Accord Healthcare Ltd',

if(wildmatch(lower(CustomerName),'actavis*') or wildmatch(lower(CustomerName),'watson*'),'Actavis Pharma Company',

if(wildmatch(lower(CustomerName),'almus*') ,'Almus Farmaceutica, S.A',CustomerName))) as Customer_Grouping

prma7799
Master III
Master III
Author

Hi  Robert,

I have one excel in that i got grouping field called CustomerGrouping. and I want to link these with customer table.

PFA....

settu_periasamy
Master III
Master III

Check the Attachment..

Kushal_Chawda

I think you have not provided correct field name for linking

Rename Customer_Names to Customer_Name

prma7799
Master III
Master III
Author

Hi Settu,

Thanks for your help,

Can you please explain me this

(ooxml, embedded labels, table is final, filters(Replace(4, top, StrCnd(null))));

Thanks