Not showing all records over & above the specific date!

[nevilledhamsiri]

Hi Experts!

Appreciate if some one help me to find out where I have gone wrong. I need to show all records (Policies placed before 22nd Sep 2017) .The results I get looks not perfectly alright.

Thanks In advance

Neville

[t_chetirbok]

Hi Neville,

Can you explain a little bit more?

Are those excel files your data sources? what date field are you going to use for restrict - Policies placed before 22nd Sep 2017.

can you send your script in txt or just in reply?

[mdmukramali · Accepted solution]

Hi ,

Can you try where condition like below

Where Date(Date#(SAL_FROM_DATE,'DD-MMM-YY'),'DD/MM/YYYY')<Date(MakeDate(2017,09,22),'DD/MM/YYYY');

find the attached application for ur reference,

Thanks,

Mukram.

[nevilledhamsiri]

Hi Tastiana,

What I have bolded   through a where clause is the field I need to restrict!

Neville

REFUNDS:

LOAD SAL_BRANCH_CODE, SAL_POL_STATUS, SAL_CLA_CODE1, SAL_TRAN_TYPE, SAL_REF_DATE, SAL_POLICY,

     SAL_FROM_DATE, SAL_TO_DATE, SAL_REF_2, SAL_DEBT_CODE, SAL_RP_CODE, SAL_CURRENCY, SAL_AMT1,

     SAL_AMT2, SAL_AMT3, SAL_AMT4, SAL_AMT5, SAL_AMT6, SAL_AMT7, SAL_AMT8, SAL_PRD_CODE,

     SAL_CLA_CODE, SAL_BUI_CHANNEL, SAL_REF_C, SAL_ENDR_NUM, SAL_ENDR_CON, SAL_EXCHANGE, SAL_AMT,

     SAL_AMT9, SAL_SUM_INSURED, SAL_DESCRIPTION, CF_TOTAL, CF_SAL_POL_STATUS, CS_POL_AMT1,

     CS_POL_AMT2, CS_POL_AMT3, CS_POL_AMT8, CS_POL_AMT7, CS_POL_AMT6, CS_POL_AMT5, CS_POL_TOT,

     CS_POL_AMT4

FROM (ooxml, embedded labels, table is Sheet1) WHERE SAL_FROM_DATE < '09/22/2017' ;

GL_BALANCE:

LOAD PAYEE, REF_NO, DATE, DR, CR

FROM (biff, embedded labels, table is Sheet1$);

Left Join (REFUNDS)

Load

     REF_NO AS SAL_REF_2,

     CR

     Resident GL_BALANCE;

finalTable:

Load

     *,

     if(CF_TOTAL-CR=0 OR    CF_TOTAL<CR , 'PHY','NON_PHY') AS PHY_NON_PHY

Resident REFUNDS;

drop tables REFUNDS,GL_BALANCE;

[nevilledhamsiri]

Thanks Mukram,

Your solution works fine.Thanks a lot once again for responding!

Regds

Neville