topic Re: A little help in a mapping statement in QlikView

A little help in a mapping statement

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

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

from source.

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

Re: A little help in a mapping statement

techvarun — Mon, 06 Nov 2017 09:54:37 GMT

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

Re: A little help in a mapping statement

techvarun — Mon, 06 Nov 2017 10:00:29 GMT

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

Re: A little help in a mapping statement

techvarun — Mon, 06 Nov 2017 10:15:27 GMT

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

Re: A little help in a mapping statement

akpofureenughwu — Mon, 06 Nov 2017 10:58:50 GMT

Hello sir,

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

I guess it for better mapping..

Re: A little help in a mapping statement

techvarun — Mon, 06 Nov 2017 11:23:15 GMT

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

Re: A little help in a mapping statement

qlikview979 — Mon, 06 Nov 2017 12:04:03 GMT

Hi ,

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

-mahesh

Re: A little help in a mapping statement

sunny_talwar — Mon, 06 Nov 2017 12:18:30 GMT

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)

Re: A little help in a mapping statement

akpofureenughwu — Mon, 06 Nov 2017 12:57:12 GMT

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

Re: A little help in a mapping statement

prieper — Mon, 06 Nov 2017 13:11:06 GMT

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.

Re: A little help in a mapping statement

akpofureenughwu — Mon, 06 Nov 2017 13:48:30 GMT

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

Re: A little help in a mapping statement

antoniotiman — Mon, 06 Nov 2017 14:29:48 GMT

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);

Re: A little help in a mapping statement

akpofureenughwu — Tue, 07 Nov 2017 09:10:51 GMT

prieper stalwar1 antoniotiman techvarun‌

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

Regards

Re: A little help in a mapping statement

akpofureenughwu — Tue, 07 Nov 2017 09:17:14 GMT

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

Re: A little help in a mapping statement

techvarun — Tue, 07 Nov 2017 09:34:04 GMT

Share sample data

Re: A little help in a mapping statement

akpofureenughwu — Tue, 07 Nov 2017 10:11:32 GMT

Kindly find the attached file.

Re: A little help in a mapping statement

antoniotiman — Tue, 07 Nov 2017 10:24:47 GMT

TypeMap:

Mapping LOAD

[Vendor ID],Type

FROM source.xlsx;

TypeMap:

MAPPING LOAD Vendor, Type Resident Type;

Drop Table Type;

Re: A little help in a mapping statement

akpofureenughwu — Tue, 07 Nov 2017 13:22:49 GMT

Hello bro,

Any luck?

Regards