Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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  Smiley Tongue_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) > Smiley Tongue_REP_VAL_45))       

        AND TRUNC(CE_DT) <= Smiley Tongue_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

5 Replies
jpe
Not applicable

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

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  ?

hrlinder
Not applicable

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

how about as in your sql select?

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

.... as OSREC

why the script runs without error?

MarcoWedel
Not applicable

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

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

Not applicable

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

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

nagaiank
Not applicable

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

use

LOAD *,

OSPAY as OS_PAY,

OSREC as OS_REC

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

;