MAP..USING, Mapping LOAD not working for me

[lawrenceiow]

I'm trying to clean my data. I have various different "codes" which mean the same thing and I think I need to create a table to remap the codes to the correct value.

So I have created the following (this is an example, I have added comments here to explain what I think I'm doing):

//Create a lookup table. My spreadsheet has 12 columns and I just want to use two from the middle labelled as Code and newCode

codeMap:

Mapping LOAD Code, newCode FROM [My spreadsheet.xlsx]

(ooxml, embedded labels, table is HB070514)
WHERE Include='Y';

/*        Example structure...

     Include, WSDgrp, Code, newCode, grp2, grp3, ...

     Y,       N,      A1,   A,       abcd, abc, ...

     Y,       N,      A2,   A,       abcd, abc, ...

     N,       N,      B1,   B,       bcde, bcd, ...

     Y,       N,      B2,   B,       bcde, bcd, ...

     Y,       N,      B3,   B,       bcde, bcd, ...

*/

// I now have a "mapping table" called CodeMap which contains two columns named Code and newCode

// Turn mapping on
MAP Code using codeMap;

// Load data from fact tables created elsewhere, each table has a field called Code.

FactTable:

LOAD * FROM [Fact table 1.qvd] (qvd);

Concatenate LOAD * FROM [Fact table 2.qvd] (qvd);

Concatenate LOAD * FROM [Fact table 3.qvd] (qvd);

//Turn mapping off

UNMAP Code;

So, what I think should have happened by now is the contents of the field "Code" in each of the qvd files have had their values changed to the equivalents in the codeMap table. If record 1 of the qvd file has A2 in the Code field, record 1 of the now resident FactTable contains A in the Code field instead.

However, pre-viewing the data in the Table Viewer shows that A2 is still in that field.

Therefore, I'm not doing it right - the mapping load and map..using commands are not doing what I think they're supposed to do, or does map..using not work on qvd files?

[#2]

Hi, Map...Using will work only on Resident loads only. Please find the below from Help file.

The mapping is done last in the chain of events leading up to the field being stored in the internal table in QlikView. This means that mapping is not done every time a field name is encountered as part of an expression, but rather when the value is stored under the field name in the internal table. If mapping on the expression level is required, the Applymap() function has to be used instead.

[lawrenceiow]

Thanks for your very fast reply dathu.qvdathu.qv. So, I should...

1. Create the map: Mapping Load....
2. Load the data into memory: LOAD from QVD; (and repeat)
3. Apply the map: MAP Code using codeMap;
4. Turn off mapping: UNMAP Code;

Is this what you meant? The thing is, that's what I tried first after reading the Help file. That didn't work for me so I tried it as per my original post.

[swuehl]

Have you double checked that your mapping values in field Code match the loaded ones? For example, load your mapping table without MAPPING prefix with appropriate field names, linking the two tables.

Are your tables linked correctly and does filtering in list box work?

If you are mapping string values, take care of leading / trailing spaces, etc.

There are limitations using a qvd for the Mapping table, but I AFAIR, using it for loading in the field values to map should be ok.

[lawrenceiow]

Thank you for your reply swuehl.

Prior to my attempt at using apply map I have loaded the mapping table as a separate table (not a mapping load) - the two tables were joined on Code as expected. I felt it would be better to remap the Code field instead of having a separate table (though I'm thinking of sticking with what works). So, yes, I have already done as per your suggestions.

[swuehl · Accepted solution]

Seems like you might need to un-optimize your QVD load also for the fields to be mapped:

FactTable:

LOAD * FROM [Fact table 1.qvd] (qvd) WHERE 1=1;

Concatenate LOAD * FROM [Fact table 2.qvd] (qvd) WHERE 1=1;

Concatenate LOAD * FROM [Fact table 3.qvd] (qvd) WHERE 1=1;

You can also look into using APPLYMAP() function in the LOAD statement instead of MAP ... USING

[lawrenceiow]

That's it!!!  Thanks swuehl, WHERE 1=1 solved it. Brilliant.