Breaking in to different types based on certain criterions!

[nevilledhamsiri]

Hi Friends

Please help me to achieve following out put based on the attached data!

1st Issue

IF PREMIUM=PHYSICAL, IT SHOULD BE 'PHYSICAL REFUND'

IF PREMIUM=NON_PHYSICAL, IT SHOULD BE 'NON_PHYSICAL REFUND'

IF PREMIUM=PHYSICAL+NON_PHYSICAL, IT SHOULD BE 'PART REFUND'

All above fields are expressions & appreciate, if the above criterions are combined & apply it in the script so as to get the desired out put.

2 nd Issue

Also debtor field carries no DEBTORS code at some instance. (Please refer excel) in such instance, I need the ME_CODE to be appeared in the debtor field (Assume Debtor code should be the ME_CODE),

Please help me to achieve this

Regards

Neville

[vishsaggi]

Can you show one example of what your expected output should be like in your 1st case your premium = physical it should be Physical refund you mean here you want to create a flag like Physical Refund, Non Physical Refund, Part Refund?

LOAD BRANCH,

    CLASS_CODE,

    PRODUCT_CODE,

    POLICY_NO,

    NAME_OF_INSURED,

    CREDIT_NOTE_NO,

    CREDIT_NOTE_DATE,

    PERIOD_FROM,

    PERIOD_TO,

    PREMIUM,

    ADDRES,

    REASON,

    FINANCIAL_INTEREST,

    ME_CODE,

    ME_NAME,

    PHYSICAL,

    NON_PHYSICAL,

    DEBIT_NOTES,

    DEBTORS,

    IF(LEN(DEBTORS)<=5,'ME','BROKER & LEASING')AS FLAG1, DIRECT_RECEIPTS,

    IF(PREMIUM = PHYSICAL, 'Physical Refund',

    IF(PREMIUM = NON_PHYSICAL, 'NON-Physical Refund',

    IF(PREMIUM = (PHYSICAL+NON_PHYSICAL), 'Part Refund'))) AS RefundFlag,

    DEBIT_SETTLEMENTS

FROM [D\REFUND_REGISTER_ALL_BRANCHES(2018).xlsx] (ooxml, embedded labels, table is Sheet1);

2 nd Issue

Also debtor field carries no DEBTORS code at some instance. (Please refer excel) in such instance, I need the ME_CODE to be appeared in the debtor field (Assume Debtor code should be the ME_CODE),

Can you elaborate your 2nd issue with an example and expected output?

[trdandamudi]

May be as below:

LOAD BRANCH,
CLASS_CODE,
PRODUCT_CODE,
POLICY_NO,
NAME_OF_INSURED,
CREDIT_NOTE_NO,
CREDIT_NOTE_DATE,
PERIOD_FROM,
PERIOD_TO,
PREMIUM,
ADDRES,
REASON,
FINANCIAL_INTEREST,
ME_CODE,
ME_NAME,
PHYSICAL,
NON_PHYSICAL,
If((PHYSICAL <> 0 AND NON_PHYSICAL =0),'PHYSICAL REFUND',
If((PHYSICAL = 0 AND NON_PHYSICAL <>0),'NON_PHYSICAL REFUND','PART REFUND' )) As Status,
If(DEBTORS='-',ME_CODE,DEBTORS) As DEBTORS, 
DEBIT_NOTES, 
IF(LEN(DEBTORS)<=5,'ME','BROKER & LEASING')AS FLAG1,
DIRECT_RECEIPTS,
DEBIT_SETTLEMENTS
FROM  < Your Table Name Here >

[nevilledhamsiri]

Yes 1st issue is something like that.

2nd one  something like below

ME_CODE  DEBTORS

7383               -

IF the case like above, under DEBTORS, 7383 should be shown as debtors

Thanks

Neville

[nevilledhamsiri]

Once, a flag was created to name Physical, non physical & part refund etc, If I am to get the ratios over three items, (such as 25%,70% & 5%) etc, how the expression to be used?

Thanks

Neville

[vishsaggi]

Where do you want to display this?

May be this?

= Num(Sum({<RefundFlag = {'Part Refund'} >}PREMIUM)/Sum({1}PREMIUM), '#,##0%')

= Num(Sum({<RefundFlag = {'NON-Physical Refund'} >}PREMIUM)/Sum({1}PREMIUM), '#,##0%')

= Num(Sum({<RefundFlag = {'Physical Refund'} >}PREMIUM)/Sum({1}PREMIUM), '#,##0%')

[nevilledhamsiri]

Hi Vishwarath,

% calculated as per your formula seems to be not working. I have redefined the script with flag .Please try your end whether results are obtained & send me the expression

Thanks

Neville

[vishsaggi]

Are you looking for this?

[nevilledhamsiri]

As per the ratio obtained, total ratio (33%) looking ok. (116101/348248*100=33%), but same answer is not found against individual cases. (Eg check 30345/87078*100=35%) but as per the working in p/table, it is 9%.

[nevilledhamsiri]

Hi Vishwarath,

Your answers for splitting following criterions are ok. Only issue being the Ratio calculations

If((PHYSICAL <> 0 AND NON_PHYSICAL =0),'PHYSICAL REFUND',
If((PHYSICAL = 0 AND NON_PHYSICAL <>0),'NON_PHYSICAL REFUND','PART REFUND' )) As Status,
If(DEBTORS='-',ME_CODE,DEBTORS) As DEBTORS,