Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All!
I have loaded two sets of data, One set which is (REFUND REGISTER) carries dimensions & expression. Premium is the only Expression. In the 2nd data set which is REFUND_GL carries CR_PREMIUM as an expression. The expression written in the script do not produce results instead an errors comes in the data loading. Can some one see how I could correct this. After expression given in the script, following expression to be used to achieve my goal!
Appreciate If some one guide me here!
Best Regards
Neville
SUM({<CLASS_CODE={'MC','M4'},[Flag Different CR_TR_MONTH]={1},ME_CODE-={'DIR'},[Flag Bigger PREMIUM]={1}>}250))))
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_MONTH<>TR_MONTH,1,0) as [Flag Different CR_TR_MONTH],
if (PREMIUM >CR_ PREMIUM,1,0) as [Flag Bigger PREMIUM]
ADDRES,
REASON,
FINANCIAL_INTEREST,
ME_CODE,
ME_NAME
FROM
Left Join
REFUND_GL:
LOAD
BRANCH,
CREDIT_NOTE_NO,
CR_PREMIUM,
FROM
The expression syntax looks correct for what you want, except that it uses "250" as the value to sum, while it should be a field name instead of a number.
Note that since you are using a JOIN, there is actually only one table in the data model, only one data set. Two sources originally (two spreadsheets in this case), one single resulting table loaded into QlikView.
Thanks for quick response. But The error comes when data are loaded. Can you please let me know the place where I have written the expression in the scrip is correct? If it is ok why error comes when it is loaded. If you show me with sample it is highly appreciated!
Rs 250 is a fine to be charged when the given criterians are satisfied which is to be given as an set expression after data being loaded
Thks
Neville
Few notes here:
Sum(If(Match(CLASS_CODE, 'MC', 'M4') > 0 AND [Flag Different CR_TR_MONTH] = 1 AND ME_CODE <> 'DIR' AND [Flag Bigger PREMIUM] = 1, 250))
Dear Miguel,
As you say if following expressions cannot be written in the script then where they are to be written.
if (CR_MONTH<>TR_MONTH,1,0) as [Flag Different CR_TR_MONTH],
if (PREMIUM >CR_ PREMIUM,1,0) as [Flag Bigger PREMIUM]
Thanks
Neville
In the script, with the form I specified in the other post:
If(Month(CREDIT_NOTE_DATE) <> Month(PERIOD_FROM), 1, 0) AS [Flag Different CR_TR_MONTH]
The line for PREMIUM must be on a second step, as at the time of using the line the tables are not yet joined, there are several ways of doing this, this is just an example that you can add after the script you posted (removing the second line in bold):
REFUND_REGISTER_FINAL:
NOCONCATENATE LOAD
*
,If(PREMIUM > CR_PREMIUM, 1, 0) AS [Flag Bigger PREMIUM]
RESIDENT REFUND_REGISTER;
DROP TABLE REFUND_REGISTER;
Thanks Miguel,
I got it!
Neville