IF MATCH IN JOINED SQL TABLES - Qlik Community

infock12 · ‎2015-07-17

Hi all,

I am using an IF(Match function to convert a text value. Basically, if an answer for a particular questions is answered as 'Yes', covert it to 'No' and vice versa. The query is below.

SELECT [QuestionNumber]
,C.Question as Questions
,Response as OriginalResponse
,IF(Match(QuestionNumber, 25, 38, 83, 103, 145, 146),

Pick(Match(Response,'Yes','No'),'No','Yes'),Response) as Response
,Rating
,[STED Category]
FROM [STED].[dbo].[Answers] A
LEFT JOIN [STED].[dbo].[Questions] C on A.QuestionNumber=C.[Question_Number]

This worked in excel where all the data is in single spread sheet and we have 1 field for Question Number. However in the SQL table, the data is split into two tables so I had to join Table A and Table C. I have a strong feeling that because it is based on the question number, Table A has the column header as 'QuestionNumber' and column C has 'Question_Number. Is this what is causing the problem, and is there a workaround for this please?

Thanks

marcus_sommer · ‎2015-07-17

I don't think that the join is a problem but you could load these tables as single tables and make the join in qv.

But what didn't worked are the pick(match()) within the sql then this are qv functions. For this you need a preceeding load like:

Load *

IF(Match(QuestionNumber, 25, 38, 83, 103, 145, 146),

Pick(Match(Response,'Yes','No'),'No','Yes'),Response) as Response;

SELECT [QuestionNumber]
,C.Question as Questions
,Response as OriginalResponse
,QuestionNumber
,Rating
,[STED Category]
FROM [STED].[dbo].[Answers] A
LEFT JOIN [STED].[dbo].[Questions] C on A.QuestionNumber=C.[Question_Number]

See also: Preceding Load

- Marcus

infock12 · ‎2015-07-17

‌Thanks Marcus. I will try that and let you know.

infock12 · ‎2015-07-17

Hi Marcus,

I tried but I am getting an error message saying Field No Found

The script is below: I have added a comma after the first Load * and added the table name "Questions" after the first Load statement. .other than that, it is exactly the same

OLEDB CONNECT TO ;

Load *,

IF(Match(QuestionNumber, 25, 83, 103,145, 146),Pick(Match(Response,'Yes','No'),'No','Yes'), Response) as Response;

Questions:

SELECT QuestionNumber,

Questions,

Response as OriginalResponse,

Rating,

FROM .[dbo].[Answers] A

LEFT JOIN .[dbo].[Questions] C on A.QuestionNumber=C.[Question_Number] Is there any Syntax that is going wrong please or not putting the right thing at the right place?

From: Marcus Sommer <qcwebmaster@qlikview.com>

To: Karthik Chinnas <karthik.chinnasamy@yahoo.com>

Sent: Friday, 17 July 2015, 17:32

Subject: Re: - IF MATCH IN JOINED SQL TABLES

|

IF MATCH IN JOINED SQL TABLES

reply from Marcus Sommer in New to QlikView - View the full discussionI don't think that the join is a problem but you could load these tables as single tables and make the join in qv. But what didn't worked are the pick(match()) within the sql then this are qv functions. For this you need a preceeding load like: Load *IF(Match(QuestionNumber, 25, 38, 83, 103, 145, 146), Pick(Match(Response,'Yes','No'),'No','Yes'),Response) as Response; SELECT

,C.Question as Questions

,Response as OriginalResponse

,QuestionNumber

,Rating

,[STED Category]

FROM .[dbo].[Answers] A

LEFT JOIN .[dbo].[Questions] C on A.QuestionNumber=C.[Question_Number] See also: Preceding Load - Marcus

Reply to this message by replying to this email, or go to the message on Qlik Community

| Start a new discussion in New to QlikView by email or at Qlik Community

Following IF MATCH IN JOINED SQL TABLES in these streams: Inbox

Report Inappropriate Content · ‎2015-07-17

You changed field Response as OriginalResponse in SELECT and using Response in LOAD statement. Use OriginalResponse instead.

Regards,

KKR

infock12 · ‎2015-07-17

‌Thanks KKR. I tried both but it didn't work. The error message I am getting for both is Field Not Found <Response>

sasiparupudi1 · ‎2015-07-17

Load *,

IF(Match(QuestionNumber, 25, 83, 103,145, 146),Pick(Match(OriginalResponse,'Yes','No'),'No','Yes'), OriginalResponse) as Response;

Questions:

SELECT QuestionNumber,

Questions,

Response as OriginalResponse,

Rating,

FROM .[dbo].[Answers] A

LEFT JOIN .[dbo].[Questions] C on A.QuestionNumber=C.[Question_Number] ;

hope there is a Response field in your sql query

infock12 · ‎2015-07-17

Sasidhar,

It worked, thank you very much! Not sure why it didn't work before, I tried all different options. Perhaps I might have done something wrong.

sasiparupudi1 · ‎2015-07-17

Great,please clise the thread by marking a correct answer.