topic Re: Translate a file into a mapping load in QlikView

Translate a file into a mapping load

Anonymous — Wed, 25 Jan 2017 14:56:22 GMT

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

Re: Translate a file into a mapping load

vinieme12 — Wed, 25 Jan 2017 15:02:12 GMT

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?

Re: Translate a file into a mapping load

sunny_talwar — Wed, 25 Jan 2017 15:04:21 GMT

Not sure I understand your question?

Re: Translate a file into a mapping load

Anonymous — Wed, 25 Jan 2017 15:22:44 GMT

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

Re: Translate a file into a mapping load

Anonymous — Wed, 25 Jan 2017 15:23:13 GMT

Hi Sunny,

Have given more details.

Matt

Re: Translate a file into a mapping load

vinieme12 — Wed, 25 Jan 2017 15:24:02 GMT

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

Re: Translate a file into a mapping load

Anonymous — Wed, 25 Jan 2017 15:28:27 GMT

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

Re: Translate a file into a mapping load

sunny_talwar — Wed, 25 Jan 2017 15:29:01 GMT

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;

Re: Translate a file into a mapping load

Anonymous — Thu, 26 Jan 2017 16:28:49 GMT

Worked perfectly and exactly what was needed.

Thank you.