How do i write case statements in Load scripts? I have a code table based on which the column RUNTIME_CATEGORY gets the values.Based on RUNTIME column values a new column which has RUNTIME_CATEGORY values has to be dipslay
Here is my Select sql :
SQL SELECT * FROM (
WITH TEMP as ( select distinct ALIAS_NAME,FULL_NAME,DEPT_DESC,DEPT_MANAGER_NAME from DW_T_CORP_EMPLOYEE where ACTIVE_FLG ='Y' ) select * from DW_RFA_JOBDATA A left outer join TEMP B on A.RFA_USER_DESC = B.ALIAS_NAME WHERE A.RFA_SUBMIT_TIME >= SYSDATE - 1);
I tried writing the sql select as below with Case statements but i get an error saying From keyword not found..So thought to find out if there is a way to write case statements in Load statement itself.
SQL SELECT * FROM (
WITH TEMP as ( select distinct ALIAS_NAME,FULL_NAME,DEPT_DESC,DEPT_MANAGER_NAME from DW_T_CORP_EMPLOYEE where ACTIVE_FLG ='Y' ) select *, (Case When A.RFA_SIMULATION_RUN_TIME >0 and A.RFA_SIMULATION_RUN_TIME <= 5 THEN '0 - 5 min' When A.RFA_SIMULATION_RUN_TIME >5 and A.RFA_SIMULATION_RUN_TIME <= 15 THEN '5 - 15 min' When A.RFA_SIMULATION_RUN_TIME >15 and A.RFA_SIMULATION_RUN_TIME <= 30 THEN '15 - 30 min' When A.RFA_SIMULATION_RUN_TIME >30 and A.RFA_SIMULATION_RUN_TIME <= 60 THEN '30 - 1 hr')AS RunTime_Category from DW_RFA_JOBDATA A left outer join TEMP B on A.RFA_USER_DESC = B.ALIAS_NAME WHERE A.RFA_SUBMIT_TIME >= SYSDATE - 1);
If it's valid SQL, it should work, because the "SQL" keyword says to pass it on to your database system. Different database systems may implement the CASE statement differently. In the version of DB2 that we use, a CASE statement looks like this:
SELECT CASE WHEN ITPTYP = '1TFS' THEN 'TFS' WHEN ITPTYP = '2TFS' THEN 'TFS' ELSE 'TIN' END AS PRODUCT , SUBSTR(CHAR(CSEVDT),1,6) AS MONTH , CSQY_21 AS RUN_TIME
There are differences here, such as the use of END. Perhaps you are just missing an END?
If you want to do the same thing in QlikView itself, for something this simiple, I'd write a nested if().
SQL SELECT * FROM ( WITH TEMP as ( select distinct ALIAS_NAME,FULL_NAME,DEPT_DESC,DEPT_MANAGER_NAME from DW_T_CORP_EMPLOYEE where ACTIVE_FLG ='Y' ) select *, //Case When A.RFA_SIMULATION_RUN_TIME >'0' and A.RFA_SIMULATION_RUN_TIME <='5' THEN '0 - 5 min' // When A.RFA_SIMULATION_RUN_TIME >'5' and A.RFA_SIMULATION_RUN_TIME <= '15' THEN '5 - 15 min' // When A.RFA_SIMULATION_RUN_TIME >'15' and A.RFA_SIMULATION_RUN_TIME <= '30' THEN '15 - 30 min' // When A.RFA_SIMULATION_RUN_TIME >'30' and A.RFA_SIMULATION_RUN_TIME <='60' THEN '30 - 1 hr'end AS RunTime_Category from DW_RFA_JOBDATA A left outer join TEMP B on A.RFA_USER_DESC = B.ALIAS_NAME WHERE A.RFA_SUBMIT_TIME >= SYSDATE - 5);
I don't think this is quite literally true, but think of everything after the SQL keyword as being passed directly to your ODBC driver and to your database system without being interpreted by QlikView. SQL uses CASE, not IF. So if you put it on the SQL side, you need to use a CASE statement. If you put it on the QlikView side (in the LOAD), then you need to use the if() function. SQL SELECT uses SQL syntax. QlikView LOAD uses QlikView syntax.
The only syntax error I see is that Interaction Type needs to either be in quotes or brackets, either "Interaction Type" or [Interaction Type]. I always use double quotes, but preferred QlikView syntax appears to be brackets, since that's what is automatically generated in several situations, and I remember at least one situation where that syntax was required.
