SQL script convert to Qlikview script for use with QVDs , please help!

[sbeaty]

Hello all. I am really new to Qlik view scripting, can someone convert my SQL code to Qlik view code please? I need something to reference from for all my other apps. I am trying to decrease our data load time. Thank you so much!  Even if someone could convert the examiners load, that helps a lot! 

Examiners:
LOAD
ExaminerID,
UserID,
Firstname as EX_FirstName,
Lastname as EX_LastName,
Active as EX_Active,
TierType as EX_TierType,
CallTakers as EX_CallTakers;
SQL SELECT ExaminerID, RTRIM(LOWER(UserID)) AS UserID, Firstname, Lastname, Active,
CASE WHEN ISNULL(TierType,-1) = -1 THEN 'No Tier Assigned' ELSE cast(TierType as varchar(20)) END AS TierType,
CASE WHEN Examiner.UserID IN ('runderwood','nhill','pmcfadden','mmorris','sdavid','bboyd') OR Examiner.TierType = 1
THEN 'Call Takers' ELSE NULL END AS CallTakers
FROM Examiner
WHERE (Examiner.ExaminerID NOT IN (88,190));
STORE Examiners into [lib://3_QVD_Generators\
DROP Tables Examiners;

ShoreTel:
LOAD
ID as ST_CallID,
CallsID as ST_CallsID,
SIPCallId as ST_SIPCallID,
UserID,
AgentName as ST_AgentName,
AgentNumber as ST_Extension,
GroupName as ST_GroupName,
AgentConnectTime as ST_StartTime,
AgentDisconnectTime as ST_EndTime,
NewInboundCall as ST_NewInboundCall,
InboundCall as ST_InboundCall,
OutboundCall as ST_OutboundCall,
ExtToExtCall as ST_ExtToExtCall,
HeldCall as ST_HeldCall,
Timestamp(RingTime,'h:mm:ss') as ST_RingTime,
Timestamp(TalkTime,'h:mm:ss') as ST_TalkTime,
Timestamp(HoldTime,'h:mm:ss') as ST_HoldTime,
ConnectReason as ST_ConnectReason,
DisconnectReason as ST_DisconnectReason,
Weekday as ST_Weekday,
KnownFlag as ST_KnownCaller,
CtrlPartyID as ST_CtrlPartyID,
CtrlPartyIDName as ST_CtrlPartyName;
SQL 
create table #tempKnownPhones(AgencyID bigint, CustomerID bigint, CallerID varchar(20))
insert into #tempKnownPhones
SELECT DISTINCT AgencyID, NULL AS CustomerID,
CASE WHEN (LEN(Telephone1) = 11 AND Telephone1 LIKE '+1%')
THEN (SUBSTRING(Telephone1, 2, 3)
+ '-' + SUBSTRING(Telephone1, 5, 3)
+ '-' + SUBSTRING(Telephone1, 8, 4))
WHEN LEN(Telephone1) = 10
THEN (SUBSTRING(Telephone1, 1, 3)
+ '-' + SUBSTRING(Telephone1, 4, 3)
+ '-' + SUBSTRING(Telephone1, 7, 4))
ELSE Telephone1 END AS CallerID
FROM Agencies
WHERE (Telephone1 <> '')
UNION ALL
SELECT DISTINCT NULL AS AgencyID, CustomerID,
CASE WHEN (LEN(Telephone1) = 11 AND Telephone1 LIKE '+1%')
THEN (SUBSTRING(Telephone1, 2, 3)
+ '-' + SUBSTRING(Telephone1, 5, 3)
+ '-' + SUBSTRING(Telephone1, 8, 4))
WHEN LEN(Telephone1) = 10
THEN (SUBSTRING(Telephone1, 1, 3)
+ '-' + SUBSTRING(Telephone1, 4, 3)
+ '-' + SUBSTRING(Telephone1, 7, 4))
ELSE Telephone1 END AS CallerID
FROM Customer
WHERE (Telephone1 <> '') AND (Customer.RepairHouse = 1)

create table #tempFirstConnect(CallsID bigint, minConnectID bigint)
insert into #tempFirstConnect

DROP TABLE #tempKnownPhones
DROP TABLE #tempFirstConnect;
STORE ShoreTel into [lib://3_QVD_Generators\
DROP Tables ShoreTel;

[sasikanth]

HI @sbeaty , 

Try below script for Examiner table load.

Examiners:
LOAD
ExaminerID,
UserID,
Firstname as EX_FirstName,
Lastname as EX_LastName,
Active as EX_Active,
IF( ISNULL(TierType), 'No Tier Assigned',TierType) as EX_TierType,
IF( (rtrim(lower(UserID))='runderwood' OR rtrim(lower(UserID))='nhill'
OR rtrim(lower(UserID))='pmcfadden' OR rtrim(lower(UserID))='mmorris'
OR rtrim(lower(UserID))='sdavid' OR rtrim(lower(UserID))='bboyd' )
OR TierType=1, 'Call Takers',NULL
)
as EX_CallTakers

SQL SELECT ExaminerID,
UserID,
Firstname,
Lastname,
Active,
TierType,
CallTakers
FROM Examiner
WHERE (Examiner.ExaminerID NOT IN (88,190));
STORE Examiners into [lib://3_QVD_Generators\
DROP Tables Examiners;

Thanks, 

Sasi

[sbeaty]

Hi, I tried it and it gave me an error saying that the ExaminerID field isn't found

[sasikanth]

@sbeaty ,

try below one

Examiners:
LOAD
ExaminerID,
UserID,
Firstname as EX_FirstName,
Lastname as EX_LastName,
Active as EX_Active,
IF( ISNULL(TierType), 'No Tier Assigned',TierType) as EX_TierType,
IF( (rtrim(lower(UserID))='runderwood' OR rtrim(lower(UserID))='nhill'
OR rtrim(lower(UserID))='pmcfadden' OR rtrim(lower(UserID))='mmorris'
OR rtrim(lower(UserID))='sdavid' OR rtrim(lower(UserID))='bboyd' )
OR TierType=1, 'Call Takers',NULL
)
as EX_CallTakers

SQL SELECT  *
FROM Examiner
WHERE (Examiner.ExaminerID NOT IN (88,190));
STORE Examiners into [lib://3_QVD_Generators\
DROP Tables Examiners;

[sbeaty]

That didn't work either. Field 'NULL' not found the error said. I appreciate the help.

[sasikanth · Accepted solution]

below script should work, give a try

Examiners:
LOAD
ExaminerID,
UserID,
Firstname as EX_FirstName,
Lastname as EX_LastName,
Active as EX_Active,
IF( ISNULL(TierType), 'No Tier Assigned',TierType) as EX_TierType,
IF( (rtrim(lower(UserID))='runderwood' OR rtrim(lower(UserID))='nhill'
OR rtrim(lower(UserID))='pmcfadden' OR rtrim(lower(UserID))='mmorris'
OR rtrim(lower(UserID))='sdavid' OR rtrim(lower(UserID))='bboyd' )
OR TierType=1, 'Call Takers',NULL()
)
as EX_CallTakers

SQL SELECT  *
FROM Examiner
WHERE (Examiner.ExaminerID NOT IN (88,190));
STORE Examiners into [lib://3_QVD_Generators\
DROP Tables Examiners;

[sbeaty]

Thank you!!