Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

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

Not applicable
Author

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

Not applicable
Author

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;

Capture.PNG

Not applicable
Author

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

Anonymous
Not applicable
Author

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