topic Re: IF MATCH IN JOINED SQL TABLES in QlikView

IF MATCH IN JOINED SQL TABLES

infock12 — Fri, 17 Jul 2015 16:07:20 GMT

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

Re: IF MATCH IN JOINED SQL TABLES

marcus_sommer — Fri, 17 Jul 2015 16:31:55 GMT

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]

Re: IF MATCH IN JOINED SQL TABLES

infock12 — Fri, 17 Jul 2015 16:48:52 GMT

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

Re: IF MATCH IN JOINED SQL TABLES

infock12 — Fri, 17 Jul 2015 18:26:07 GMT

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

Re: IF MATCH IN JOINED SQL TABLES

Fri, 17 Jul 2015 18:32:03 GMT

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

Regards,

KKR

Re: IF MATCH IN JOINED SQL TABLES

infock12 — Fri, 17 Jul 2015 18:38:04 GMT

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

Re: IF MATCH IN JOINED SQL TABLES

sasiparupudi1 — Fri, 17 Jul 2015 18:56:36 GMT

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

Re: IF MATCH IN JOINED SQL TABLES

infock12 — Fri, 17 Jul 2015 19:16:18 GMT

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.

Re: IF MATCH IN JOINED SQL TABLES

sasiparupudi1 — Fri, 17 Jul 2015 19:21:01 GMT

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