topic Re: changing exp into a calculated dimension in QlikView

changing exp into a calculated dimension

sindhura — Thu, 18 Feb 2016 20:11:00 GMT

I have a status field as expression in a straight table which contains

=if(CancelledDate<>Null(),'Cancelled'

,if(_TriageDate<>Null(),'Processed'

,if(_Rctp_Date<>Null() ,'Received'

,if(Len(ord.ShipDate)=0,'InStore','Shipped'))))

and my users want to filter that one.

so i changed it to calculated dimension, but after changing to calculated dimension, i find nulls in place of instore. why is this happening

Re: changing exp into a calculated dimension

sunny_talwar — Thu, 18 Feb 2016 21:44:55 GMT

Not sure I understand the issue. Do you have a sample you can share?

Re: changing exp into a calculated dimension

marcus_sommer — Fri, 19 Feb 2016 08:38:46 GMT

A possible reason might be that you have sometimes more then one unique date-value (maybe several different ship-dates for one order) for your dimension-values and your check contained no aggregation - only field = value. Is there more then one unique value NULL will be returned. This meant you need to apply an aggregation or an error-handler with alt(), maybe:

Len(max(ord.ShipDate)) // required an additional aggr-wrapping within a dimension

alt(Len(ord.ShipDate), 0)

- Marcus

Re: changing exp into a calculated dimension

jonathandienst — Fri, 19 Feb 2016 08:55:24 GMT

This part of the expression is incorrect. It will always return false (in expression or as a dimension):

CancelledDate<>Null()

The correct way to look for nulls is

IsNull(CancelledDate)

Len(CancelledDate) = 0

Re: changing exp into a calculated dimension

jagan — Fri, 19 Feb 2016 08:57:08 GMT

HI,

Try like this

=if(Not IsNull(CancelledDate),'Cancelled'

,if(Not IsNull(_TriageDate),'Processed'

,if(Not IsNull(_Rctp_Date) ,'Received'

,if(Len(Trim(ord.ShipDate))=0,'InStore','Shipped'))))

Regadrs,

jagan.

Re: changing exp into a calculated dimension

sindhura — Fri, 19 Feb 2016 15:24:27 GMT

Thanks all for responding me. I have with all the ways you people were suggesting but nothing helped out.

I was able to see all the status except the default one i.e "Instore".

Re: changing exp into a calculated dimension

sindhura — Fri, 19 Feb 2016 15:48:29 GMT

See in the image I can see Instore in the expression and in the text object but not in Listbox or in calculated Dimension eventhough the expression is same

Re: changing exp into a calculated dimension

marcus_sommer — Fri, 19 Feb 2016 16:59:44 GMT

I think you should provide a sample.

- Marcus

Re: changing exp into a calculated dimension

sindhura — Fri, 19 Feb 2016 21:31:16 GMT

Hi Everyone,

Thanks for helping with the possible chances.

I have used flags in the scripting like

if(Len(Trade_ShipDate)<>0,1,0) as _ShippedDateFlag

if(CancelledDate<>Null(),1,0) as _CancelledDateFlag,

if(TriageDate<>Null(),1,0) as _ProcessedDateFlag,

if(Rctp_Date<>Null(),1,0) as _ReceivedDateFlag,

and changed the expression as

if(_CancelledDateFlag=1,'Cancelled'

,if(_ProcessedDateFlag=1,'Processed'

,if(_ReceivedDateFlag=1 ,'Received'

,if(_ShippedDateFlag=1,'Shipped'

,'InStore'))))

Finally it worked.

Thanks for the response and keep Helping:):)

Re: changing exp into a calculated dimension

jagan — Mon, 22 Feb 2016 01:25:17 GMT

Hi,

If you got the Answer please close this thread by giving Correct and Helpful answers.

Regards,

jagan.