Solved: Scripting - Qlik Community

upaliwije · ‎2019-04-12

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,

jonathandienst · ‎2019-04-17

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,

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

PrashantSangle · ‎2019-04-12

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

sunny_talwar · ‎2019-04-12

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?

upaliwije · ‎2019-04-12

Thanks Sunny

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

upaliwije · ‎2019-04-12

Thanks Sunny

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

sunny_talwar · ‎2019-04-12

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,

upaliwije · ‎2019-04-13

Thanks sunny,

now the result is like shown below

upaliwije · ‎2019-04-16

Dear Sunny,

Can you help me please

sunny_talwar · ‎2019-04-17

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

marcus_sommer · ‎2019-04-17

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

Scripting

Related Topics