topic Re: Scripting in QlikView

Scripting

upaliwije — Fri, 12 Apr 2019 10:08:15 GMT

Hi Friends

I have following table in my QV documnet, In my data load script I have created the field 'POLICY_PERIOD'combining the two fields 'POL_PERIOD_FROM' and 'POL_PERIOD_TO' with following syntax.

DUAL( DATE(POL_PERIOD_FROM,'MMM-YY') &' - '& DATE(POL_PERIOD_TO,'MMM-YY'),POL_PERIOD_FROM) AS POLICY_PERIOD,

However in the policy period column ending period shown as Mar-17 although actual ending period is Sep-16. I can not understand how that happened and kindly correct me if I have done anything wrong,

Re: Scripting

PrashantSangle — Fri, 12 Apr 2019 11:22:19 GMT

try with simple
1: MonthName(POL_PERIOD_FROM)&'-'&MonthName(POL_PERIOD_TO)
2: Date(POL_PERIOD_FROM,'MMM-YY') &'-'& Date(POL_PERIOD_TO,'MMM-YY')

Regards,

Re: Scripting

sunny_talwar — Fri, 12 Apr 2019 11:28:33 GMT

It is strange how it worked for the other two rows but not for this one.... would you be able to share a sample where we can see the issue?

Re: Scripting

upaliwije — Fri, 12 Apr 2019 16:27:41 GMT

Thanks Sunny

I attach h/w sample data please. pls advise me

Re: Scripting

upaliwije — Fri, 12 Apr 2019 16:27:52 GMT

Thanks Sunny

I attach h/w sample data please. pls advise me

Re: Scripting

sunny_talwar — Fri, 12 Apr 2019 16:39:01 GMT

Can you try this

Dual(
     Date(POL_PERIOD_FROM, 'MMM-YY') 
     & ' - ' &
     Date(POL_PERIOD_TO, 'MMM-YY')
, POL_PERIOD_FROM+POL_PERIOD_TO) as POLICY_PERIOD,

Re: Scripting

upaliwije — Sat, 13 Apr 2019 17:39:09 GMT

Thanks sunny,

now the result is like shown below

Re: Scripting

upaliwije — Wed, 17 Apr 2019 03:06:45 GMT

Dear Sunny,

Can you help me please

Re: Scripting

sunny_talwar — Wed, 17 Apr 2019 11:48:11 GMT

I am not really sure I understand this behavior, may be @marcus_sommer or @rwunderlich can help you here.

Re: Scripting

marcus_sommer — Wed, 17 Apr 2019 12:48:05 GMT

It looked like an unexplainable bug ... and doing the same within the UI worked like expected. I think I would re-create the qvd-source and ensure that the from/to-fields there are loaded equally maybe with floor(to).

- Marcus

Re: Scripting

jonathandienst — Wed, 17 Apr 2019 12:55:00 GMT

I think I know what the problem is. A dual value can only have one possible text representation for a numerical value. The real value of the dual is the numeric - the text is only the display representation of the value.

I expect that you have a POLICY_PERIOD value somewhere else for POL_PERIOD_FROM = 31 March (Mar-16 - Mar-27). In the model, the dual will be stored as a numeric with a single display format. So the display value for 31 March 2016 is the value in POLICY_PERIOD.

If you want to correctly reflect you would have to create a numeric value that considers both the _FROM and _TO fields. You could try this if you want to return a valid date value that reflects the FROM date:

Dual(Date(POL_PERIOD_FROM, 'MMM-YY') 
     & ' - ' &
     Date(POL_PERIOD_TO, 'MMM-YY'), 
     POL_PERIOD_FROM + POL_PERIOD_TO/100000
) as POLICY_PERIOD,