Translate a file into a mapping load

[Anonymous]

Hi all,

Have a problem, which is causing an issue.

I want to load in a table which has lots of values. These codes will translate to a text field, that I want to apply in bulk.

Example table:

Mapping Table	Raw code	New Value
Country	a	UK
Country	b	USA
Country	c	IRE
Car Make	1	Fiat
Car Make	2	Ford
Car Make	3	Ferrari

My aim is to turn these into two (and will be many many more) mapping tables to apply map over the field shown in mapping table.

Example:

[Mapping Table_Map]:

Mapping load

[Raw code]

[New Value]

Resident File;

IS this possible?

I have tried a few ways and with no luck. Can someone please help me??

Any help will be massively appreciated.

Matt

[vinieme12]

Sorry Matthew still don't understand what is the problem here, you seem to be familiar with Applymap() and Mapping load , so what exactly are looking to do?

[sunny_talwar]

Not sure I understand your question?

[Anonymous]

Hi Vineeth,

I am familiar with the functions.

My problem is the data example provided is taking from a load from a colleague and I wish to translate this (in bulk, as there are 60k+ rows) into multiple applymaps.

1 for country, 1 for car make and so on.

I hope this is more information.

I thought do loop and while iter() would be useful but i am getting no luck.

Matt

[Anonymous]

Hi Sunny,

Have given more details.

Matt

[vinieme12]

if everything is being mapped using the RawCode as a key then why not use Left Join instead of multiple Mapping loads?

[Anonymous]

I am trying to achieve something similar to SAS's use of formats where I have a raw code which i want to replace.

Join will not be suitable on this occasion.

Thanks for the suggestion though.

Matt

[sunny_talwar · Accepted solution]

May be this:

Table:

LOAD * INLINE [

    Mapping Table, Raw code, New Value

    Country, a, UK

    Country, b, USA

    Country, c, IRE

    Car Make, 1, Fiat

    Car Make, 2, Ford

    Car Make, 3, Ferrari

];

FOR i = 1 to FieldValueCount('Mapping Table')

  LET vField = FieldValue('Mapping Table', $(i));

  [$(vField)Mapping]:

  Mapping

  LOAD [Raw code],

  [New Value]

  Resident Table

  Where [Mapping Table] = '$(vField)';

NEXT

DROP Table Table;

[Anonymous]

Worked perfectly and exactly what was needed.

Thank you.