(Q): replacing field values in a table with 3300 cols (like with mapping)

[obmk]

Hello,

I do have (all in all) 6 tables (Excel) with about 3.300 columns each. This structure is identical and I allready made a qvd and loaded the data resident.

This table represents a questionaire where each column carries the answer (to be exact: its an integer AnswerID ) to a specific question. Each row represents one asked person. The labels of the Quesions as well as the Answer Labels are stored in a seprarate Excel file with a complete different structure. All Quesion elements (QuestionID, AnswerID, Question Label, Product Groups, CountryIDs etc) all extracted via LOAD commands ans stored in separate tables.

I'm looking for: an efficient way to replace the AnswerIDs vom the huge table with the corresponding Answer Label so that I get a, let's say, one big answer sheet with clear text answers.

I tried Mapping but each mapping has to be made for each quesiton and i have more than 3.000.

Thanks in advance for your hints.

Best

Snoman

[Miguel_Angel_Baeyens · Accepted solution]

Hello Snoman,

Here is my suggested answer, using crosstable plus applymap. It's very simple, though.

Hope that helps!

[obmk]

Hola Miguel,

thanks for the quick response. I tried the crosstable command already because the big answer grid really invites to 🙂 But you bring me to an idea that I had not tried so far....

btw: I forgot to tell that I dont have unique AnswerIDs. In fact, the AnswerID has to be mapped in context to the corresponding questionid, which containes sometimes additional information about the question context (Country, Region, product Group, male, Female)

Example: if, in a spec. question, the AnswerID ist 5, this could mean:

"I eat fast Food 5 times a week" or could mean "I'm type 5 of the given educationale status"

Therefore, I need a mapping that sets the AnswerLabel corresponding to the QuestionID.

But perhaps can I manage that with several mappings... I'll try that.