Converting one text value into another

[infock12]

Hi Everyone,

I have a peculiar problem but I am sure we have a lot of intelligent minds here who knows the answer. Table attached, simple table with two fields. ID 3, 4 and 7 has No, but the question was designed in such a way that "No" is a positive answer in that instance. The rest of the questions are fine. It is a large data but I just want to give you a simple example. How can I say those 3 questions(3,4,7) as "Yes" where  the actual answer is "No" and vice versa. Can I do anything in load script or write a variable? Please help

[marcus_sommer]

You could do within the load something like this:

...

if(match(ID, 3,4,7), 'Yes', Answers) as Answers

...

- Marcus

[infock12]

Thanks Marcus, that is a good start. However, it should say "Yes" only when those questions are "No" and it should say "No" when they are "Yes". I am counting these with another field. The formula goes like this.

=count({<Response={'Yes'}, Rating{>=4, <=7}>} ID)

Currently, I am getting the total of "YES", "NO" but obviously getting it wrong.

[sinanozdemir]

Something like this maybe:

Here is the data model:

[MarcoWedel · Accepted solution]

Hi,

you could change Markus' answer like:

LOAD ID,
    Answers as OriginalAnswers,
    If(Match(ID,3,4,7),Pick(Match(Answers,'Yes','No'),'No','Yes'),Answers) as Answers
FROM [https://community.qlik.com/servlet/JiveServlet/download/814878-174478/questions.xlsx]
(ooxml, embedded labels, table is Sheet1);

hope this helps

regards

Marco

[infock12]

Thanks Sinan and Marco!! Both works like a charm!! Thanks for your help.

[MarcoWedel]

you're welcome.

Please close your thread.

thanks

regards

Marco