topic Re: Data grouping in QlikView

Data grouping

prma7799 — Mon, 28 Dec 2015 06:01:04 GMT

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 .

Re: Data grouping

Anonymous — Mon, 28 Dec 2015 06:15:42 GMT

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

Thanks

BKC

Re: Data grouping

settu_periasamy — Mon, 28 Dec 2015 06:18:31 GMT

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

Re: Data grouping

robert_mika — Mon, 28 Dec 2015 06:21:17 GMT

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)

Re: Data grouping

Anonymous — Mon, 28 Dec 2015 06:21:39 GMT

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

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!

Re: Data grouping

Kushal_Chawda — Mon, 28 Dec 2015 06:30:30 GMT

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

Re: Data grouping

prma7799 — Mon, 28 Dec 2015 06:41:10 GMT

Hi Robert,

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

PFA....

Re: Data grouping

settu_periasamy — Mon, 28 Dec 2015 06:54:47 GMT

Check the Attachment..

Re: Data grouping

Kushal_Chawda — Mon, 28 Dec 2015 06:59:03 GMT

I think you have not provided correct field name for linking

Rename Customer_Names to Customer_Name

Re: Data grouping

prma7799 — Mon, 28 Dec 2015 07:12:43 GMT

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

Re: Data grouping

settu_periasamy — Mon, 28 Dec 2015 07:19:18 GMT

Your excel is file Merged with Rows (some data) in the Customer Grouping Name filed.

We can fill the Null values the using the Enable Transformation Wizard.. See the below picture..

this will create the script, filters(Replace(4, top, StrCnd(null))));

Re: Data grouping

prma7799 — Tue, 29 Dec 2015 05:11:03 GMT

Hi settu_periasamy‌ , Kush141087‌

Settu I am using your solution, getting blank values in table box

please see below

Re: Data grouping

settu_periasamy — Tue, 29 Dec 2015 05:19:34 GMT

Yes, There is 137 customers only available in your "CustomerGrouping" Excel File. But, In your Customer QVD file contains nearly 2000 customers.

Re: Data grouping

prma7799 — Tue, 29 Mar 2016 06:32:48 GMT

Hisettu_periasamy‌,

What will I do for that 2000 customers which is showing me null.

Thanks