topic Converting one text value into another in QlikView

Converting one text value into another

infock12 — Mon, 06 Jul 2015 18:36:07 GMT

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

Re: Converting one text value into another

marcus_sommer — Mon, 06 Jul 2015 18:47:31 GMT

You could do within the load something like this:

...

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

...

- Marcus

Re: Converting one text value into another

infock12 — Mon, 06 Jul 2015 19:15:00 GMT

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.

Re: Converting one text value into another

sinanozdemir — Mon, 06 Jul 2015 20:03:02 GMT

Something like this maybe:

Here is the data model:

Re: Converting one text value into another

MarcoWedel — Mon, 06 Jul 2015 23:40:48 GMT

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

Re: Converting one text value into another

infock12 — Tue, 07 Jul 2015 08:18:52 GMT

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

Re: Converting one text value into another

MarcoWedel — Tue, 07 Jul 2015 10:41:19 GMT

you're welcome.

Please close your thread.

thanks

regards

Marco