How can achieve SQL Case Satetment in Qlikview

[vijaysahu2]

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

<Qlikview  code below and sample Data>

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

[vijaysahu2 · Accepted solution]

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);

[Brett_Bleess]

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/Script...

Regards,
Brett

[vijaysahu2]

Thanks Brett  for your link.. issue is resolved