Date Format -Urgent

[krishna20]

Hi ,

I'm having two dates which are in integer format . I have calculation with these dates.

Please help me to convert these dates for exact calculation. I'm having sql query based on this i converted these dates.But exact result is not getting.

SELECT

ROUND((TP_POL_TO_DT - TP_POL_FM_DT),2) DIFF,

ROUND((SYSDATE  - TP_POL_FM_DT  ),2) REMAINING_DAYS,

SUM(TP_PREM_LC_1)- SUM(TP_DISC_LC_1) + SUM(TP_LOAD_LC_1) GROSS_PREM,

FROM T_GI_POLICY

WHERE

TP_COMP_CODE='001'

AND TP_CUST_CODE='111502'

GROUP BY TP_POL_FM_DT,TP_POL_TO_DT

Result is :

365

104.51

1018848

I'm converting one of the date above query as below

=(Round(Date(Today(),'DD/MM/YYYY'),2)-Round(Date(TP_POL_FM_DT,'DD/MM/YYYY'),2)) as Remaining Days

My Result is  : 364 Instead of 365 and  102 Instead of 104.51 I'm getting this. Please help me out

Regards

Krishna

[#2]

tRY LIKE BELOW,

SELECT

INTERVAL(TP_POL_TO_DT - TP_POL_FM_DT,'DD') as  DIFF,

INTERVAL(SYSDATE  - TP_POL_FM_DT,'DD') as REMAINING_DAYS,

SUM(TP_PREM_LC_1) - SUM(TP_DISC_LC_1) + SUM(TP_LOAD_LC_1) aS GROSS_PREM,

FROM T_GI_POLICY...

ALSO,

INTERVAL(Date(Today(),'DD/MM/YYYY')-Date(TP_POL_FM_DT,'DD/MM/YYYY'),'DD') as Remaining Days ...

[krishna20]

Hi Harsha,

Thank you for your reply,

Its working for one expression

=INTERVAL(Date(TP_POL_TO_DT,'DD/MM/YYYY')-Date(TP_POL_FM_DT,'DD/MM/YYYY'),'DD')

Before applying Interval function my value is 364.999958 now its 364.Its perfect

and not working for

INTERVAL(Date(Today(),'DD/MM/YYYY')-Date(TP_POL_FM_DT,'DD/MM/YYYY'),'DD')

But its no change the value remains same as 103 actual value is 104.

[Or]

A few notes:

* Assuming you are interested in dates rather than timestamps, Floor() your dates instead of round()ing them.

* If you are using round(), you should use now() rather than today().

* Assuming your columns are actual dates, you shouldn't have to date() them.

[krishna20]

Hi ,

Not getting expected result as suggested.

[shambhub]

Hi Krishna,

If your dates are like Due date = 20150123 & Today = 20150415. then you can use below expression to get days difference.

  (makedate(left( Today,4),mid( Today,5,2),right( Today,2)) -

    makedate(left("Due date",4),mid("Due date",5,2),right("Due date",2))) as Days

Best Regards

Shambhu B

[Gysbert_Wassenaar]

In Qlikview the second parameter in the round function is not the number of decimals. It's the number you enter. Round( ...expression..., 2) will round up to the nearest even value. 1.10102 will round up to 2 and 2.3234 will round down to 2. If you want to use the round function to round to two decimals use Round( ...expr... , 0.01)

[krishna20]

Hi Gysbert,

Thank you for your reply.

I achieved this by doing like below in the straight table.But, when i'm running this expression in the script end.The script is not  running and raises an error as Invalid Expression.

LOAD

*,

If(Floor(TP_POL_TO_DT)>Floor(Now()),

(SUM(TP_GrossPremium_LC_1)/((Floor((TP_POL_TO_DT))-(Floor(TP_POL_FM_DT)))))*(Floor((Now()))-Floor((TP_POL_FM_DT))), TP_GrossPremium_LC_1) as TP_EarnedPremium_LC_1;

Please suggest me what mistake i have done.

[Gysbert_Wassenaar]

LOAD

*,

If(Floor(TP_POL_TO_DT)>Floor(Now()),

(SUM(TP_GrossPremium_LC_1)/((Floor((TP_POL_TO_DT))-(Floor(TP_POL_FM_DT)))))*(Floor((Now()))-Floor((TP_POL_FM_DT))), TP_GrossPremium_LC_1) as TP_EarnedPremium_LC_1;

1. That statement is missing a source reference. Where does the input data come from?

     LOAD ...stuff... FROM MySourceTable

     or LOAD ...stuff... RESIDENT TableCreatedEarlierInTheScript

2. You're using the SUM function without a Group By clause.

[krishna20]

Hi Gysbert,

I have written the script in the table itself. I think no need to mention resident keyword for the script. Correct me if i'm wrong and please suggest me where i need to write Group By Clause and what are the fields it includes?

Please find the screen shot of my script.