Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
sunny_talwar

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
vinieme12
Champion III
Champion III

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?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
sunny_talwar

Not sure I understand your question?

Anonymous
Not applicable
Author

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
Not applicable
Author

Hi Sunny,

Have given more details.

Matt

vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

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

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
Not applicable
Author

Worked perfectly and exactly what was needed.

Thank you.