topic Re: How can achieve SQL Case Satetment in Qlikview in QlikView

How can achieve SQL Case Satetment in Qlikview

vijaysahu2 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All

I am trying to convert the SQL code in Qlikview Script. I have loaded the Qvd file from SQL server data and now want to use the Case condition on a column to replace the value.

I have SQL code like below

SELECT DISTINCT CASE WHEN u.username LIKE '%Jel%' THEN UPPER(REPLACE(U.UserName,'JEL\',''))

WHEN u.username LIKE '%CLOUD%' THEN UPPER(REPLACE(U.UserName,'CLOUD\',''))

ELSE '' END AS 'LINK'

FROM UserTable

CaseTesting:
LOAD * INLINE [
UserName, Values
Jel\Vijay.sahu, 1
Jel\Amit.Singh, 2
CLOUD\admin , 3
];
STORE CaseTesting INTO 'CaseTesting.qvd' (QVD);

How can I apply the similar case statement on Username column so the result should like below

UserName, Value

VIJAY.SAHU , 1

AMIT.SINGH, 2

ADMIN, 3

Please suggest

Re: How can achieve SQL Case Satetment in Qlikview

vijaysahu2 — Sun, 05 May 2019 18:35:03 GMT

After too much research, Finally I am able to replicate the SQL code in Qlikview Script. This will help others
Solution :

CaseTesting:
LOAD * INLINE [
UserName, Values
Jel\Vijay.sahu, 1
Jel\Amit.Singh, 2
CLOUD\admin , 3
ad,4
];
STORE CaseTesting INTO 'CaseTesting.qvd' (QVD);

Drop Table CaseTesting;

CaseReplace:
//
//LOAD IF (WildMatch(UserName,'*CLOUD\*'),UPPER(REplace(UserName,'CLOUD\',''))) AS UserName;
LOAD IF (WildMatch(UserName,'*Jel\*'),UPPER(REplace(UserName,'Jel\','')),(IF (WildMatch(UserName,'*CLOUD\*'),UPPER(REplace(UserName,'CLOUD\',''))))) AS UserName,
Values
FROM
CaseTesting.qvd
(qvd);

Re: How can achieve SQL Case Satetment in Qlikview

Brett_Bleess — Mon, 24 Jun 2019 14:12:36 GMT

Check the following Help link if you are still stuck on this one, may help:

http://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptControlStatements/Switch.htm

Regards,
Brett

Re: How can achieve SQL Case Satetment in Qlikview

vijaysahu2 — Mon, 24 Jun 2019 20:19:02 GMT

Thanks Brett for your link.. issue is resolved