A little help in a mapping statement

[akpofureenughwu]

Hello sir,

I have been using the mapping statement but I'm encountering a challenge.

Here is the structure of the data

FIRS

Date	Company	State	Document Number	TIN	Cost
01/01/2016	XYZ	Edo	AD1232FD	3423r	1200
01/01/2016	Joe OUTSOURCING LIM	Edo	AF3454GF	1230d	200
01/02/2017	Aerobrown Enterp	Delta	DFg454fd	1245f	100

Type

Company	Type
XYZ	Cold
Joe OUTSOURCING LIMITED	Hot
Aerobrown Enterprise	Cold

Here is my script:

TypeMap:

Mapping LOAD

    Company,

    "Type"

from source.....

[DATE] AS [Document Date],

    Company,

    ApplyMap('TypeMap', 'Company',null()) as Type,

    [State],

    XXXX

    XXXX

from source.

some how the type is blank after running the script, Please what do you think? 

   .

[akpofureenughwu]

prieper‌

Thank you for commenting... Company Code is the deal!! I just review the sheets. I can map with the company code. Let me check it out. Will be back.

Thanks 

[antoniotiman]

May be like this

MapTable:
Mapping
LOAD Left(Company,If(Index(Company,' ',-1) > 0,Index(Company,' ',-1),999)) as Company,
'{<'&Type&'>}'
FROM
https://community.qlik.com/message/1375658
(html, codepage is 1252, embedded labels, table is @2);
LOAD Date,
Company,
State,
[Document Number],
TIN,
Cost,TextBetween(MapSubString('MapTable',Company),'{<','>}') as Type
FROM
https://community.qlik.com/message/1375658
(html, codepage is 1252, embedded labels, table is @1);

[akpofureenughwu]

prieper stalwar1 antoniotiman techvarun‌

I want to use vendor id as the mapping variable. Thank you for your contributions

Regards 

[akpofureenughwu]

Hey

I reviewed the script to use vendor id as the mapping variable since the vendor id is constant even if the data entry operator spell the company's name irregularity. I edit the script you proposed but I'm having an error.

Check it out:

BP:

load*,

if( Isnum(Date), 'Q')& Ceil(Month(Date)/3) as Quarter,

'Day '&Day(Date)&' '&Month as [Day Number];

load*,

Year("Date") as Year,

Month("Date") as Month,

Day("Date") as Day;

FIRS:

LOAD

    "DATE",

    Company,

          [State],

     if("State" = 'DT','Delta',

     if("State" = 'ED','Edo',

     if("State" = 'LA','Lagos',

     if("State" = 'RV','Rivers',

     if("State"= 'FC','FCT',

     if("State"= 'AB','Asaba',

      if("State"= 'OG','Ogun',

      if("State"= 'OY','Oyo',

      if("State"= 'KD','Kaduna',

       if("State"= 'EB','Ebonyi',

     if("State" = 'IM','IMO',"State"))))))))))) as [State Group],

    State,

    FIRS,

    Account,

    SGN,

    "Document Number",

    Vendor,

    "Profit Ctr",

    "TIN Number",

    "Type",

    "Amount in doc. curr.",

    Curr.,

    "Amount in local currency",

    LCurr,

    PK,

    "Pstng Date",

    date( date#([Pstng Date],'DD.MM.YYYY'),'DD/MM/YYYY')  as Date

FROM source1.xlsx;

Type:

Mapping LOAD

    "Type",

    Vendor

FROM source.xlsx;

TypeMap: 

MAPPING LOAD Vendor, Type Resident Type; 

Drop Table Type;

Load     ApplyMap('TypeMap', Vendor,null()) as Type,* Resident FIRS;

DROP TABLE FIRS

Please help me revieew

[techvarun]

Share sample data

[akpofureenughwu]

Kindly find the attached file.

[antoniotiman]

TypeMap:

Mapping LOAD

    [Vendor ID],Type

FROM source.xlsx;

TypeMap: 

MAPPING LOAD Vendor, Type Resident Type; 

Drop Table Type;

[akpofureenughwu]

Hello bro,

Any luck?

Regards