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

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

Fri, 30 Sep 2016 14:50:22 GMT

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!

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

sunny_talwar — Fri, 30 Sep 2016 14:58:46 GMT

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

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

Fri, 30 Sep 2016 15:12:52 GMT

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

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

sunny_talwar — Fri, 30 Sep 2016 15:13:52 GMT

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

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

marcus_sommer — Fri, 30 Sep 2016 15:13:55 GMT

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

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

sunny_talwar — Fri, 30 Sep 2016 15:14:57 GMT

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

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

Fri, 30 Sep 2016 15:23:02 GMT

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

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

sunny_talwar — Fri, 30 Sep 2016 15:31:57 GMT

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;

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

Fri, 30 Sep 2016 15:50:25 GMT

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.

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

Anonymous — Fri, 30 Sep 2016 15:52:40 GMT

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)

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

sunny_talwar — Fri, 30 Sep 2016 16:00:36 GMT

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;

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

marcus_sommer — Fri, 30 Sep 2016 16:10:39 GMT

This could be done within a loop, like:

for i = 0 to nooftables()

let vTableName = tablename($(i));

for ii = 1 to nooffields('$(vTableName)')

let vFieldName = fieldname($(ii), '$(vTableName)');

MAP $(vFieldName) USING YNMap;

- Marcus

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

Fri, 30 Sep 2016 16:27:53 GMT

Great, Sunny

That's what I'm looking for.

Thanks a lot!

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

Fri, 30 Sep 2016 16:29:31 GMT

Hello, Marcus

The Sunny's approach was more suitable for me.

Thanks anyway