topic preceding load with sql statement column names are not loading in Properties -> Available fields in QlikView

preceding load with sql statement column names are not loading in Properties -> Available fields

Tue, 26 Aug 2014 14:54:10 GMT

OS_NET :

LOAD

OSPAY as OS_PAY,

OSREC as OS_REC

where CE_CLOSE_DT>Today() AND CLM_CLOSE_DT<= Today()

;

SQL SELECT comp_short_name,trunc(CLM_CLOSE_DT),TRUNC(CE_CLOSE_DT),CLM_RISK_SR_NO,CE_RISK_SR_NO,NVL(SUM(DECODE(CE_EST_TYPE, '1', NVL(CE_AMT_LC_1, 0), 0) - DECODE(CE_EST_TYPE, '3', NVL(CE_AMT_LC_1, 0), 0)), 0) OSPAY,

NVL(SUM(DECODE(CE_EST_TYPE, '2', NVL(CE_AMT_LC_1, 0), 0) - DECODE(CE_EST_TYPE, '4', NVL(CE_AMT_LC_1, 0), 0)), 0) OSREC

FROM t_gi_claim, T_gi_clm_est, m_company

WHERE clm_trans_id = CE_CLM_TRANS_ID

AND (NVL(CLM_STATUS,'N') <> 'C' OR (NVL(CLM_STATUS, 'N') = 'C' AND :P_REP_VAL_45 <= TRUNC(CLM_CLOSE_DT)))

AND NVL(CLM_STATUS,'N') <> 'R'

AND (NVL(CE_CLOSE_YN, '0') = '0'

OR (NVL(CE_CLOSE_YN, '0') = '1' AND TRUNC(CE_CLOSE_DT) > :P_REP_VAL_45))

AND TRUNC(CE_DT) <= :P_REP_VAL_45

AND CLM_RISK_SR_NO = CE_RISK_SR_NO

AND CLM_SOURCE_FM=CE_SOURCE_FM

AND CLM_LOB_CODE=CE_LOB_CODE

--and CLM_TRANS_ID=CE_CLM_TRANS_ID

AND CLM_COMP_CODE=CE_COMP_CODE

and clm_close_dt is not null

and CLM_COMP_CODE= comp_code group by comp_short_name,trunc(CLM_CLOSE_DT),CLM_RISK_SR_NO,CE_RISK_SR_NO,TRUNC(CE_CLOSE_DT);

i want to display OSPAY as OS_PAY, OSREC as OS_REC . The script executes fine without any error but the OSPAY as OS_PAY, OSREC as OS_REC is not listed in properties -> available fields

Re: preceding load with sql statement column names are not loading in Properties -> Available fields

JonnyPoole — Tue, 26 Aug 2014 15:06:12 GMT

Strange question but if you go to the table viewer (CTRL-T) are the fields in the OS_NET table ? Is there a OS_NET table ?

Re: preceding load with sql statement column names are not loading in Properties -> Available fields

Anonymous — Tue, 26 Aug 2014 15:26:54 GMT

how about as in your sql select?

.... as OSPAY (your definition ...,0) OSPAY)

.... as OSREC

why the script runs without error?

Re: preceding load with sql statement column names are not loading in Properties -> Available fields

MarcoWedel — Tue, 26 Aug 2014 22:19:26 GMT

maybe there are no records meeting your conditions and thus no fields are generated.

Re: preceding load with sql statement column names are not loading in Properties -> Available fields

Wed, 27 Aug 2014 02:36:10 GMT

Please refresh the qvw file and look into fields. Please use Table viewer ( Ctrl + T )

Re: preceding load with sql statement column names are not loading in Properties -> Available fields

nagaiank — Wed, 27 Aug 2014 02:39:45 GMT

use

LOAD *,

OSPAY as OS_PAY,

OSREC as OS_REC

where CE_CLOSE_DT>Today() AND CLM_CLOSE_DT<= Today()

;