topic Re: Creating grouping field on same name..... in QlikView

Creating grouping field on same name.....

prma7799 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have one table customer and it has custNo and custName with 2000 records now I want link or add new one field called CustGroup using one excel file It is also have custNo and custName but 246 records only.

Please see expected output in attached.

Re: Creating grouping field on same name.....

avinashelite — Mon, 11 Apr 2016 05:55:23 GMT

Create a Mapping table and and use the apply map function to get the desired output.

Group_Mapping:

LOAD * inline

[

Name,Group

A,A_Group

.....

...

];

LOAD Applymap('Group_Mapping',Name_filed) as Group,

from

table

;

Re: Creating grouping field on same name.....

Kushal_Chawda — Mon, 11 Apr 2016 05:59:30 GMT

try this

LOAD No,

Name,

if(wildmatch(lower(Name),'aspen*'),'Aspen Group',

if(wildmatch(lower(Name),'actavis*'),'Actavis Group',Name)) as Group

FROM Table

Re: Creating grouping field on same name.....

prma7799 — Mon, 11 Apr 2016 06:13:31 GMT

Hi Avinash,

There is no any automatic way to create grouping filed.

And If your solution will work then what will happen entire customer which is present fact table (2000 customer).

Thanks

Re: Creating grouping field on same name.....

avinashelite — Mon, 11 Apr 2016 06:26:47 GMT

As per the attached file format you don't have a specific way to group the Names

for eg

1.Actavis Pharma, Inc. has Grouping Actavis

2.Actavis NZ Ltd has Grouping as Accord Healthcare Ltd.

please provide a way to group the names so that we can help you out with the grouping otherwise you need build a manual mapping tables that s the only way

Re: Creating grouping field on same name.....

prma7799 — Mon, 11 Apr 2016 06:36:18 GMT

I have created like this

Group_Mapping:

LOAD * Inline [

Customer , Group

ALJABAL DRUGS & MEDICAL ,ALJABAL

Actavis Group PTC , Bulgaria ,Actavis

Actavis Pharma Company ,Actavis

Accord Healthcare Ltd. ,Accord

ACTAVIS PHARMA INC ,Actavis

Accord Healthcare SA ,Accord

Actavis US ,Actavis

Actavis Pharma, Inc. ,Actavis

Actavis NZ Ltd ,Actavis

];

GL:

LOAD *, Applymap('Group_Mapping',Customer_Name) as Group;

LOAD AccLoc_Code,

Amount,

Budget_Amt,

BusSeg_Code,

Customer_Country_GL,

Customer_Name,

Customer_No,

Div_Code

FROM

FACTQVGLE.qvd

(qvd);

now GL table link with mapping table with group key

Is this right?

Re: Creating grouping field on same name.....

avinashelite — Mon, 11 Apr 2016 06:44:50 GMT

MAPPING key word is missing

Group_Mapping:

MAPPING LOAD * Inline [

Customer , Group

ALJABAL DRUGS & MEDICAL ,ALJABAL

Actavis Group PTC , Bulgaria ,Actavis

Actavis Pharma Company ,Actavis

Accord Healthcare Ltd. ,Accord

At the end of the script Group_Mapping table will automatically get deleted .....

Re: Creating grouping field on same name.....

tamilarasu — Mon, 11 Apr 2016 07:51:59 GMT

Hi,

You can also try,

Temp:
LOAD No., 
     Name,
  Upper(SubField(Name,' ',1)) as FirstName
FROM

(ooxml, embedded labels, header is 1 lines, table is Sheet1);


Left Join (Temp)
Load FirstName,
     Count(Upper(FirstName)) as Count
   Resident Temp Group by FirstName;
  
NoConcatenate
Data:
LOAD *,
     If(Count>1,Capitalize(FirstName) & ' Group',Name) as [Group Name]
   Resident Temp;

DROP Table Temp;

Result Table:

Re: Creating grouping field on same name.....

prma7799 — Mon, 11 Apr 2016 08:10:40 GMT

Hi Avinash ,

When i applied below code in production then newgroup is not showing anything

Group_Mapping:
MAPPING LOAD Name AS Customer,
Group as g
FROM
[..\Excels\PNO\CUST GROUP TEST.xlsx]
(ooxml, embedded labels, table is Sheet1);

FACT_GL_Entry:
LOAD * , Applymap('Group_Mapping',CustomerName) as newGroup;
LOAD [Entry No_] AS EntryNo,

Div_Code &'_'& num (month(([Posting Date]))) as TKye,

AUTONUMBER(/*[G_L Account No_] & '_' & */date([Posting Date],'DD/MM/YYYY')
& '_' & BusSeg_Code & '_' & AccLoc_Code & '_' & [Gen_ Bus_ Posting Group]
& '_' & [Gen_ Prod_ Posting Group] & '_' & [Location Code]
& '_' & [Document No_]) AS %GLKey,

//Name AS GLAcc_Name,
      Div_Code,
Dept_Code,
[Document Type],
[G_L Account No_] AS GLAcc_Code,
//[Posting Date],
     //[Document No_],
     //Description AS GL_Desc,
     //[Bal_ Account No_],
     Amount AS GL_Amount,
//BusSeg_Code,
     //AccLoc_Code,
     Quantity AS GL_Qty,

date([Document Date],'DD/MM/YYYY') AS GL_DocDate,

[Source Code],
IsProduct,
Vendor_No,
Vendor_Name as VendorName,
Customer_No,
Customer_Name as CustomerName
,[Customer_Country_GL]
,[Customer_Region_GL]
FROM D:\Production\Qvds\NAVQVDS\FACTQVGLE.qvd (qvd);

Left join (FACT_GL_Entry)
Load GLAcc_Code,
GLAcc_name,
GL_Market_Seg,
GL_Buss_Seg,
GL_Buss_Seg1,
Type1,
TYPE,
[Note No],
[Note Name],
Subsch1,
Subsch2,
GLAcc_Code1,
MISFLAG
FROM
[..\Excels\PNO\GL Account Master_Customized_NEW.xlsx]
(ooxml, embedded labels, table is Sales);

join (FACT_GL_Entry)
//DIM_Division_Region:
LOAD HOD,
[Business Segment],
Region as Div_Region,
[Country / Division] as Div_Name ,
[Division Code] as Div_Code
FROM
[..\Excels\PNO\Master File - Region Division 09.03.16.xls]
(biff, embedded labels, table is Final$);

join (FACT_GL_Entry)
//DIM_Division_Master:
LOAD

Div_Code,

Div_Name as DIVNAME

FROM
[..\Qvds\NAVQVDS\DIMQVDivision.qvd]
(qvd);

Thanks

Re: Creating grouping field on same name.....

avinashelite — Mon, 11 Apr 2016 09:03:18 GMT

Check the format because Qlikview is case sensitive and it need to match exactly ..and the CustomerName column should have the similar key as Name i.e Customer

Group_Mapping:
MAPPING LOAD Upper(Name) AS Customer,
Group as g
FROM
[..\Excels\PNO\CUST GROUP TEST.xlsx]
(ooxml, embedded labels, table is Sheet1);

FACT_GL_Entry:
LOAD * , Applymap('Group_Mapping',Upper(CustomerName)) as newGroup

Re: Creating grouping field on same name.....

prma7799 — Mon, 11 Apr 2016 09:27:17 GMT

Tried this but nothing happen ...