Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
upaliwije
Creator II
Creator II

Scripting

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,

Screenshot_1.png

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,

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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

10 Replies
PrashantSangle

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

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
Creator II
Creator II
Author

Thanks Sunny

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

upaliwije
Creator II
Creator II
Author

Thanks Sunny

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

sunny_talwar

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
Creator II
Creator II
Author

Thanks sunny,

now the  result is like shown below

 

Screenshot_1.png

upaliwije
Creator II
Creator II
Author

Dear Sunny,

Can you help me please

sunny_talwar

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

marcus_sommer

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