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? 

   .

[techvarun]

Try below script

FIRS:
LOAD * INLINE [
    Date, Company, State, Document Number, TIN, Cost
    01/01/2016, XYZ, Edo, AD1232FD, 3423r, 1200
    01/01/2016, Joe OUTSOURCING LIMITED, Edo, AF3454GF, 1230d, 200
    01/02/2017, Aerobrown Enterprise, Delta, DFg454fd, 1245f, 100
];




TypeMap:
MAPPING LOAD * INLINE [
    Company, Type
    XYZ, Cold
    Joe OUTSOURCING LIMITED, Hot
    Aerobrown Enterprise, Cold
];








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


DROP TABLE FIRS

[techvarun]

Hi There is no need of a single-quote for the fieldname(Company in your case) in Applymap Statement

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

Fields should exactly match to apply map perfectly

Joe OUTSOURCING LIM in your table FIRS will not identify Joe OUTSOURCING LIMITED in the mapping table

Similarly  Aerobrown Enterpwill not identify Aerobrown Enterprise

Thanks

Varun

[techvarun]

Still if you want to achieve this without changing the code then use below script

FIRS:
LOAD *,Left(Company1,15) as Company INLINE [
    Date, Company1, 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:
 LOAD * INLINE [
    Company, Type
    XYZ, Cold
    Joe OUTSOURCING LIMITED, Hot
    Aerobrown Enterprise, Cold
];


TypeMap:
MAPPING LOAD Left(Company,15) as Company, Type Resident Type;
Drop Table Type;


LOAD     ApplyMap('TypeMap', Company,null()) as Type,* Resident FIRS;  


DROP TABLE FIRS

[akpofureenughwu]

Hello sir,

Can you explain the left(company, 15) as company?

I guess it for better mapping..

[techvarun]

Instead of picking the whole field value the Left() function will pick only the 15 characters from the left

[qlikview979]

Hi ,

left(company, 15) :- It will give the First 15 characters from company column.

-mahesh

[sunny_talwar]

Your mapping table is made up of complete company names like LIMITED, Enterprise, whereas your original table includes abbreviations.... why don't you abbreviate in your mapping table also? or alternatively, use complete company names in the fact table (don't really seem realistic)

[akpofureenughwu]

Sunny, Thank you for your post.

The fact table is a transaction table that holds over 40,000 lines of data. The mapping table is a table that contains the name and the type. Abbreviating the mapping table  isn't the best idea because we want to keep the standard.

Regards

[prieper]

Was your initial example a real one, or does it contain type-errors?

How else should there be a connection made betw "Joe OUTSOURCING LIM" and "Joe OUTSOURCING LIMITED"?

As Sunny said, would rather expect a company-code, rather than a descriptive field.