Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

matthewdavies
New Contributor III

Translate a file into a mapping load

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 TableRaw codeNew Value
CountryaUK
CountrybUSA
CountrycIRE
Car Make1Fiat
Car Make2Ford
Car Make3Ferrari

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

1 Solution

Accepted Solutions
Highlighted

Re: Translate a file into a mapping load

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;

View solution in original post

8 Replies
Highlighted
vinieme12
Esteemed Contributor II

Re: Translate a file into a mapping load

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?

Highlighted

Re: Translate a file into a mapping load

Not sure I understand your question?

Highlighted
matthewdavies
New Contributor III

Re: Translate a file into a mapping load

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

Highlighted
matthewdavies
New Contributor III

Re: Translate a file into a mapping load

Hi Sunny,

Have given more details.

Matt

Highlighted
vinieme12
Esteemed Contributor II

Re: Translate a file into a mapping load

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

Highlighted
matthewdavies
New Contributor III

Re: Translate a file into a mapping load

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

Highlighted

Re: Translate a file into a mapping load

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;

View solution in original post

Highlighted
matthewdavies
New Contributor III

Re: Translate a file into a mapping load

Worked perfectly and exactly what was needed.

Thank you.