Creating grouping field on same name.....

[prma7799]

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.

[avinashelite]

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

;

[Kushal_Chawda]

try this

LOAD No,

            Name,

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

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

FROM Table

[prma7799]

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

[avinashelite]

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

[prma7799]

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?

[avinashelite]

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

[tamilarasu]

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:

[prma7799]

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

[avinashelite]

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