How to add expressions to set analysis to show expected values!

[nevilledhamsiri]

Hi!

I have following dimensions!

CLASS_CODE

CR_MONTH

TR_MONTH

ME_CODE

& FOLLOWING EXPRESSIONS

CR_PREMIUM

PREMIUM

I need to form a set analysis to incorporate followings!

CLASS_CODE= MC,M4, CR_MONTH<>TR_MONTH, ME_CODE<>{DIR} & CR_PREMIUM > PREMIUM (When these conditions are met, answer to be Rs 250. I have formed a set expression & it should be ok up to cr_premium & premium combination. Please help me to add it to this & get the output.

SUM({<CLASS_CODE={'MC','M4'},CR_MONTH-=TR_MONTH,ME_CODE-={'DIR'},CR_PREMIUM>PREMIUM>}250))))

Thanks

Neville

[felipedl]

On your load do:

Load

CLASS_CODE

CR_MONTH

TR_MONTH

ME_CODE

& FOLLOWING EXPRESSIONS

CR_PREMIUM

PREMIUM,

if (CR_MONTH<>TR_MONTH,1,0) as [Flag Different CR_TR_MONTH],

if (CR_PREMIUM > PREMIUM,1,0) as [Flag Bigger CR_PREMIUM]

From [Whatever]

SUM({<CLASS_CODE={'MC','M4'},[Flag Different CR_TR_MONTH]={1},ME_CODE-={'DIR'},[Flag Bigger CR_PREMIUM]={1}>}250))))

[nevilledhamsiri]

Dear philip!

Thanks for your feed back but I could not tel you that I have to load the said data in two separate loading as below. In addition to fields loaded, I have to create two months extracted from credit_note_date & period_from then I have to use it for the expression what you have proposed! But once I applied your proposed expression in the script a error comes in data loading. Can you please correct me where I have gone wrong.

Thanks in advance

Neville

REFUND_REGISTER:

LOAD

BRANCH,

CLASS_CODE,

PRODUCT_CODE,

POLICY_NO,

NAME_OF_INSURED,

CREDIT_NOTE_NO,

CREDIT_NOTE_DATE,

MONTH(CREDIT_NOTE_DATE) AS CR_MONTH,

PERIOD_FROM,

MONTH(PERIOD_FROM) AS TR_MONTH,

PERIOD_TO,

PREMIUM,

ADDRES,

REASON,

FINANCIAL_INTEREST,

ME_CODE,

ME_NAME

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

REFUND_GL:

LOAD

BRANCH AS 1 ,

CREDIT_NOTE_NO,

CR_PREMIUM,

IF (CR_PREMIUM > PREMIUM,1,0) as [Flag Bigger CR_PREMIUM],

IF(CR_MONTH<>TR_MONTH,1,0) as [Flag Different CR_TR_MONTH]

FROM (ooxml, embedded labels, table is Sheet2);

[nevilledhamsiri]

Sorry it is Felip not the philip, sorry for the mistake done!

[nevilledhamsiri]

Please help on this

Neville

[Anil_Babu_Samineni]

Normally, you need to setup like

SUM({<CLASS_CODE={'MC','M4'},CR_MONTH-=TR_MONTH,ME_CODE-={'DIR'},CR_PREMIUM={"= CR_PREMIUM>PREMIUM"}>}250))))

[nevilledhamsiri]

Dear Anil,

Thanks  a lot, it worked!

Could you please explain me the facts I explained to felip, where I got an error when two tables are loaded. The steps proposed by Mr Felip are ok but when two sets of data are loaded, I have no idea where it should be written.

Thanks

Neville

[nevilledhamsiri]

Anil,

When I write the condition  in the script as you propose to use in the set analysis, the error comes, may be because I have linked two tables where two expressions are in tow tables. Could you please correct me where I have done wrong!

Thanks

Neville

REFUND_REGISTER:

LOAD

BRANCH,

CLASS_CODE,

PRODUCT_CODE,

POLICY_NO,

NAME_OF_INSURED,

CREDIT_NOTE_NO,

CREDIT_NOTE_DATE,

MONTH(CREDIT_NOTE_DATE) AS CR_MONTH,

PERIOD_FROM,

MONTH(PERIOD_FROM) AS TR_MONTH,

PERIOD_TO,

PREMIUM,

IF(CR_PREMIUM>PREMIUM,1,0) AS FLAG,

ADDRES,

REASON,

FINANCIAL_INTEREST,

ME_CODE,

ME_NAME

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

Left Join

REFUND_GL:

LOAD

BRANCH AS 1 ,

CREDIT_NOTE_NO,

CR_PREMIUM,

FROM (ooxml, embedded labels, table is Sheet2);

[Anil_Babu_Samineni]

You have CR_PREMIUM field in the right table and you are doing flag left join. That is the reason it throws error.

[nevilledhamsiri]

Dear Anil,

Now I wrote it in the right table but still error comes in the data loading

Please check

Regds

Neville

REFUND_REGISTER:

LOAD

BRANCH,

CLASS_CODE,

PRODUCT_CODE,

POLICY_NO,

NAME_OF_INSURED,

CREDIT_NOTE_NO,

CREDIT_NOTE_DATE,

MONTH(CREDIT_NOTE_DATE) AS CR_MONTH,

PERIOD_FROM,

MONTH(PERIOD_FROM) AS TR_MONTH,

PERIOD_TO,

PREMIUM,

ADDRES,

REASON,

FINANCIAL_INTEREST,

ME_CODE,

ME_NAME

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

Left Join

REFUND_GL:

LOAD

BRANCH AS 1 ,

CREDIT_NOTE_NO,

CR_PREMIUM,

IF(CR_PREMIUM>PREMIUM,1,0) AS FLAG

FROM (ooxml, embedded labels, table is Sheet2);