topic Re: SET ANALYSIS EXPRESSION in QlikView

SET ANALYSIS EXPRESSION

yadav_anil782 — Mon, 07 Sep 2015 10:35:56 GMT

Dear comunity,

plz helpt to write correct script for this condision- sum of "AMOUNT_PAID"

WITH CONDISION

INVOICE_TYPE =STANDERED

LINE_TYPE_LOOKUP_CODE =ITEM

Line_lookup_code = NULL

AMOUNT_PAID	INVOICE_TYPE	LINE_TYPE_LOOKUP_CODE	Line_lookup_code
900	STANDARD	freight	india
3600	debit	ITEM
6000	STANDARD	freight	india
10000	debit	ITEM
20400	debit	freight
25242	STANDARD	ITEM	india
461173	STANDARD	freight
1699999	STANDARD	ITEM	india

Re: SET ANALYSIS EXPRESSION

alexandros17 — Mon, 07 Sep 2015 10:42:07 GMT

Try with:
sum({$ <INVOICE_TYPE={'STANDARD'}, LINE_TYPE_LOOKUP_CODE={'ITEM'}>} If(IsNull(Line_lookup_code), AMOUNT_PAID, 0))

let me know

Re: SET ANALYSIS EXPRESSION

Mon, 07 Sep 2015 10:42:25 GMT

Hi,

=sum({<INVOICE_TYPE ={'STANDERED'},LINE_TYPE_LOOKUP_CODE ={'ITEM'},Line_lookup_code={'NULL'}

>}AMOUNT_PAID)

Re: SET ANALYSIS EXPRESSION

Anonymous — Mon, 07 Sep 2015 10:44:47 GMT

Hi Anil,

Load

sum(AMOUNT_PAID) as AMOUNT_PAID ,

INVOICE_TYPE,

LINE_TYPE_LOOKUP_CODE

from...

where

INVOICE_TYPE = {'STANDARD'} and LINE_TYPE_LOOKUP_CODE = {'ITEM'} and len(trim(Line_lookup_code)) <> 0

group by INVOICE_TYPE,LINE_TYPE_LOOKUP_CODE

Regards

Neetha

Re: SET ANALYSIS EXPRESSION

Anonymous — Mon, 07 Sep 2015 10:48:29 GMT

sum({<INVOICE_TYPE ={'STANDARD'},LINE_TYPE_LOOKUP_CODE ={'ITEM'},LINE_LOOKUP_CODE={''}>}AMOUNT_PAID)

Re: SET ANALYSIS EXPRESSION

Mon, 07 Sep 2015 10:52:31 GMT

Hi try this

=sum({<INVOICE_TYPE ={'STANDERED'},LINE_TYPE_LOOKUP_CODE ={'ITEM'},Line_lookup_code={"=len(trim(Line_lookup_code))<=0"}>}AMOUNT_PAID)

thanks

manju

Re: SET ANALYSIS EXPRESSION

tresB — Mon, 07 Sep 2015 11:03:00 GMT

If I take your sample data, it doesn't really contain a NULL(which set analysis can't catch and not selectable in QV, but yours is selectable here ).

You can try :

=Sum({<INVOICE_TYPE={STANDARD},LINE_TYPE_LOOKUP_CODE={ITEM},Line_lookup_code-={''}>} AMOUNT_PAID)

Re: SET ANALYSIS EXPRESSION

sasiparupudi1 — Mon, 07 Sep 2015 11:59:03 GMT

I think you need two statements

may be try this

sum({$ <INVOICE_TYPE={'STANDARD'}, LINE_TYPE_LOOKUP_CODE={'ITEM'}>} AMOUNT_PAID)+sum({$ <Line_lookup_code ={"=len(trim(Line_lookup_code))=0"},LINE_TYPE_LOOKUP_CODE-={'ITEM'}>} AMOUNT_PAID)

the statement might show error line but it should still work .

hth

Sasi

Re: SET ANALYSIS EXPRESSION

Anonymous — Mon, 07 Sep 2015 13:14:41 GMT

Hi Anil, the difficult part here is the NULL for Line_lookup_code. It is not easy to simply use set analysis for this. You can use an IF statement as follows -

=sum(if(INVOICE_TYPE = 'STANDARD' and LINE_TYPE_LOOKUP_CODE = 'ITEM' and isnull( Line_lookup_code), AMOUNT_PAID))

Or you can simply set your null values for the field in script and use this in your set analysis - you can use set null values or - as follows:-

if(isnull(Line_lookup_code),'NULL',Line_lookup_code) as Line_lookup_code,

then you can use the new value in your set analysis as follows:-

=sum({<INVOICE_TYPE = {'STANDARD'},LINE_TYPE_LOOKUP_CODE = {'ITEM'},Line_lookup_code ={'NULL'}>} AMOUNT_PAID)

Hope this helps.

Re: SET ANALYSIS EXPRESSION

yadav_anil782 — Tue, 08 Sep 2015 08:39:14 GMT

Thanks Jeremy , its working now