How to modify the same values in more than hundred distinct fields?

[#1]

Hello everyone!

I have a data base with more than hundred fields, each one with exactly the same values sets.

I have to change some values on every field. For example, "OK" instead of "Yes", as I show on the example below:

* It's not the real situation above (I've created this example just to illustrate the problem. In fact, the real fields are a lot of different questions, as "Do you like to work here?")

How can I do this through Qilkview's load script?

I know if I have to do this in just one field is quite simple. But if I want to do this in more than hundred distinct fields at once?

Tks!

[sunny_talwar]

May be doing a CrossTable might help and then to bring it back you might be able to use Generic load or something similar.

[#3]

I've tried this. But I need the original structure.

[sunny_talwar]

You will get back the original structure. Let me get you a working sample

[marcus_sommer]

I think this could be done with a mapping like here: Filling Default Values Using Mapping | Qlikview Cookbook by replacing NULL with a real value - changing YES into OK should be work similary.

- Marcus

[sunny_talwar]

Wow, this is much simpler then my long approach. I am going to hold off to working on a sample

[#7]

But on your example we have a few fields to mapping. It's quite simple:

MAP Shipped USING YNMap;

MAP BackOrdered USING YNMap;

MAP ProductDesc USING MissingMap;

On my situation, I have hundreds fields to mapping...

[sunny_talwar]

Something like this is what i was proposing

Table:

CrossTable(Question, Answer)

LOAD * INLINE [

    ID, Question1, Question2, Question150

    ID01, Yes, No, Maybe

    ID02, Maybe, Yes, Maybe

    ID03, No, Yes, Maybe

    ID04, No, Yes, Maybe

    ID05, No, No, Maybe

    ID06, Yes, No, Maybe

    ID07, Maybe, No, No

    ID08, Yes, Yes, Yes

    ID2560, Yes, Yes, Yes

];

NextTable:

NoConcatenate

LOAD ID,

  Question,

  If(Answer = 'Yes', 'OK', Answer) as Answer

Resident Table;

FinalTable:

LOAD Distinct ID

Resident NextTable;

FOR i = 1 to FieldValueCount('Question')

  LET vQuestion = FieldValue('Question', $(i));

  Left Join (FinalTable)

  LOAD ID,

  Answer as Question$(i)

  Resident NextTable

  Where Question = '$(vQuestion)';

NEXT

DROP Tables Table, NextTable;

[#9]

Whow, Sunny!

Almost there. In fact, as I pointed before, the real fields names aren't Question1, Question2, Question3...

I have to keep the real questions as fields names, as I have in my data base. For example: "Do you believe working here for the next 5 years?" as one field name.

[Anonymous]

no tested.....may be something like this ...

SOURCE:

LOAD ID,

     Question1,

     Question2,

     Question3

FROM

.........................);

LET VCOLNO = NoOfFields('SOURCE');  // Countng number of columns

  FOR I = 1 TO  NoOfFields('SOURCE')

   let vFieldname = fieldname$(i); // Getting in to each column(field)

       Let vNumRecords = FieldValueCount($(vFieldname));

          IF(FieldValue='Yes','Ok',FieldValue)

   next