Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

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

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:

Screen Shot 09-30-16 at 11.40 AM.JPG

* 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!

1 Solution

Accepted Solutions
Highlighted

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

Try this:


Table:

CrossTable(Question, Answer)

LOAD * INLINE [

    ID, Do you believe working here for the next 5 years?, Do you like to work here?, Do you feel motivated?

    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 [$(vQuestion)]

  Resident NextTable

  Where Question = '$(vQuestion)';

NEXT

DROP Tables Table, NextTable;

View solution in original post

13 Replies
Highlighted

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

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

Highlighted
Not applicable

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

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

Highlighted

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

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

Highlighted
MVP & Luminary
MVP & Luminary

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

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

Highlighted

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

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

Highlighted
Not applicable

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

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...



Highlighted

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

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;

Capture.PNG

Highlighted
Not applicable

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

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.

Screen Shot 09-30-16 at 12.49 PM.JPG

Highlighted
Specialist
Specialist

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

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