topic Re: How I write an expressions in the script where data are in two sets of dataa loaded! in QlikView

How I write an expressions in the script where data are in two sets of dataa loaded!

nevilledhamsiri — Wed, 25 Nov 2020 16:16:04 GMT

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 (biff, embedded labels, table is Sheet1$);

Left Join

REFUND_GL:

LOAD

BRANCH,

CREDIT_NOTE_NO,

CR_PREMIUM,

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

Re: How I write an expressions in the script where data are in two sets of dataa loaded!

Miguel_Angel_Baeyens — Tue, 31 Oct 2017 16:02:40 GMT

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.

Re: How I write an expressions in the script where data are in two sets of dataa loaded!

nevilledhamsiri — Tue, 31 Oct 2017 16:14:45 GMT

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

Re: How I write an expressions in the script where data are in two sets of dataa loaded!

Miguel_Angel_Baeyens — Tue, 31 Oct 2017 16:52:59 GMT

Few notes here:

The script will not work because it uses fields that do not exist at the time of loading (the lines in bold in your example), as detailed in a separate post
"250" cannot be used in a Sum() expression like that, it will return unexpected results, the fine must be stored into a value in order to be used in the Sum(), or you could use instead a condition, after which you display the 250, although I would not recommend doing so unless that is exactly what you want to do, something like:

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))

Re: How I write an expressions in the script where data are in two sets of dataa loaded!

nevilledhamsiri — Tue, 31 Oct 2017 17:17:54 GMT

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

Re: How I write an expressions in the script where data are in two sets of dataa loaded!

Miguel_Angel_Baeyens — Tue, 31 Oct 2017 17:40:09 GMT

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;

Re: How I write an expressions in the script where data are in two sets of dataa loaded!

nevilledhamsiri — Tue, 31 Oct 2017 17:55:58 GMT

Thanks Miguel,

I got it!

Neville