topic Re: Sicript help ???? in QlikView

Sicript help ????

amit_saini — Wed, 10 Dec 2014 04:35:43 GMT

Hi Folks,

I'm using Pivot table and I'm looking for solution below:

if Average Demand = „0“ and Actual Inventory Available = 0 and Actual Inventory Value= “0” then don’t show the Material number in the report.

I'm trying something like below , but not working

=IF( not isnull(ACTUAL_INV_VAL) and not isnull(ACTUAL_INV_QTY) and not isnull(AVG_DEMAND), Material_MATNR, NULL())

Thanks,

Re: Sicript help ????

Wed, 10 Dec 2014 05:09:33 GMT

Hi,

Try if(sum of all three <>0,field,0).

Re: Sicript help ????

amit_saini — Wed, 10 Dec 2014 05:30:22 GMT

Any help ????

Re: Sicript help ????

amit_saini — Wed, 10 Dec 2014 05:30:47 GMT

Not working!

Re: Sicript help ????

jagan — Wed, 10 Dec 2014 07:40:07 GMT

Hi,

You need to use this condtion in all the expressions

=IF( [Average Demand] = 0 and [Actual Inventory Available] = 0 and [Actual Inventory Value= 0], Your Expression Goes here)

Note : If all expressions returns 0 in a row then that is automatically removed, to do this we are using above If().

Hope this helps you.

Regards,

Jagan.

Re: Sicript help ????

qlikconsultant — Wed, 10 Dec 2014 08:13:42 GMT

Also check the properties of you object:

Chart Properties -> Dimensions

- Suppress When Values is NULL should be marked

- Show All Values should be unmarked.

Re: Sicript help ????

amit_saini — Thu, 11 Dec 2014 11:22:02 GMT

Hi Jagan,

Please see the attachment , I tried few things but still looks like approach is wrong.

Kindly help if possible.

Thanks,

Re: Sicript help ????

mato32188 — Thu, 11 Dec 2014 11:29:23 GMT

Hi amit,

try to use conditional show, but not sure, if it would fulfill your requirement.

Re: Sicript help ????

jagan — Fri, 12 Dec 2014 02:47:06 GMT

Hi Amit,

Please find attached file for solution, I implemented only for Currency column, do the same for remaining columns and check the presentation tab for changes.

Regards,

Jagan.

Re: Sicript help ????

amit_saini — Fri, 12 Dec 2014 05:56:06 GMT

Hi Jagan,

Thanks for your response ,Just one question:

If(Column(1) >0 AND Column(2) > 0 AND Column(3) > 3,

CURRENCY, Null())

This Column(3) > 3 should be Column(3) > 0???

Thanks,

Re: Sicript help ????

jagan — Fri, 12 Dec 2014 07:12:19 GMT

Hi Amit,

It is 0 not 3, typo mistake.

If(Column(1) >0 AND Column(2) > 0 AND Column(3) > 0,

CURRENCY, Null())

Regards,

Jagan.

Re: Sicript help ????

amit_saini — Fri, 12 Dec 2014 07:20:06 GMT

Hi Jagan,

Still its not working the way I want , please see below:

=if(AVG_DEMAND>0 and ACTUAL_INV_VAL >0 and ACTUAL_INV_QTY>0,Material_MATNR,Null())

If I'm using this as dimension for sure showing me material number if I'm having any value against AVG_Demand , ACTUAL_INV_VAL and ACTUAL_INV_QTY but if any one of them is having value and others are 0 than also Material number should appear.

Any Help here?

Thanks,

Re: Sicript help ????

jagan — Fri, 12 Dec 2014 08:27:02 GMT

Hi,

Don't use this in dimension, try the way I provided in my earlier post, if all the rows returns 0/null then the Material number automatically removed when you supress 0 and Null values in the Presentation tab.

Regards,

Jagan.

Re: Sicript help ????

amit_saini — Fri, 12 Dec 2014 08:46:27 GMT

Thanks u so much , it is working now .

I Salute u Sir

Thanks,
AS

Re: Sicript help ????

jagan — Fri, 12 Dec 2014 09:00:42 GMT

Its my pleasure to help you people.

Regards,

Jagan.

Re: Sicript help ????

amit_saini — Fri, 12 Dec 2014 12:59:38 GMT

Hi Jagan,

Just to share with you , I got one more logic and it is working fine if I'm calling material number as dimmension:

LOAD

COMP_CODE,

PLANT,

PROFIT_CTR as Profit_Center_key,

PROD_GRP,

//right([MATNR],10) as [Material_MATNR],

IF( not isnull(ACTUAL_INV_VAL) and not isnull(ACTUAL_INV_QTY) and not isnull(AVG_DEMAND), right([MATNR],10), NULL()) as [Material_MATNR],

MAKTX,

DATE_HORIZON,

If(AVG_DEMAND=''or AVG_DEMAND='-',0,AVG_DEMAND) as AVG_DEMAND ,

If(ACTUAL_INV_VAL=''or ACTUAL_INV_VAL='-',0,ACTUAL_INV_VAL) as ACTUAL_INV_VAL,

If(ACTUAL_INV_QTY=''or ACTUAL_INV_QTY='-',0,ACTUAL_INV_QTY) as ACTUAL_INV_QTY,

CUST_DEMAND,

HORIZON,

VALUE_PER_PC,

//ACTUAL_INV_VAL,

CURRENCY,

//ACTUAL_INV_QTY,

UNIT,

ACT_INV_QTY_AVL,

ACTUAL_DOH,

TARGET_DOH,

TARGET_INV_QTY,

TARGET_INV_VAL,

DATE_MODIFIED as Datefield,

Date(DATE_MODIFIED,'YYYYMM') as Date_Key

FROM

(qvd)

where (If(AVG_DEMAND=''or AVG_DEMAND='-',0,AVG_DEMAND)+ If(ACTUAL_INV_VAL=''or ACTUAL_INV_VAL='-',0,ACTUAL_INV_VAL) + If(ACTUAL_INV_QTY=''or ACTUAL_INV_QTY='-',0,ACTUAL_INV_QTY)) <>0 ;

Thanks,