topic Re: Date Format -Urgent in QlikView

Date Format -Urgent

krishna20 — Wed, 15 Apr 2015 09:47:03 GMT

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

Re: Date Format -Urgent

Wed, 15 Apr 2015 10:07:02 GMT

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 ...

Re: Date Format -Urgent

krishna20 — Wed, 15 Apr 2015 10:25:17 GMT

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.

Re: Date Format -Urgent

Or — Wed, 15 Apr 2015 10:27:38 GMT

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.

Re: Date Format -Urgent

krishna20 — Wed, 15 Apr 2015 10:38:57 GMT

Hi ,

Not getting expected result as suggested.

Re: Date Format -Urgent

shambhub — Wed, 15 Apr 2015 10:42:48 GMT

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

Re: Date Format -Urgent

Gysbert_Wassenaar — Wed, 15 Apr 2015 10:59:12 GMT

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)

Re: Date Format -Urgent

krishna20 — Wed, 15 Apr 2015 11:12:58 GMT

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.

Re: Date Format -Urgent

Gysbert_Wassenaar — Wed, 15 Apr 2015 11:30:25 GMT

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.

Re: Date Format -Urgent

krishna20 — Wed, 15 Apr 2015 11:43:52 GMT

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.

Re: Date Format -Urgent

Wed, 15 Apr 2015 11:45:23 GMT

Try like below,

As Gysbert suggest like below,

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

FROM T_GI_POLICY

WHERE

TP_COMP_CODE='001'

AND TP_CUST_CODE='111502'

GROUP BY TP_POL_FM_DT,TP_POL_TO_DT

Re: Date Format -Urgent

krishna20 — Wed, 15 Apr 2015 12:00:57 GMT

Hi Harsha,

No Luck !!

Re: Date Format -Urgent

Gysbert_Wassenaar — Wed, 15 Apr 2015 12:04:35 GMT

You need to use LOAD ... RESIDENT ... if you want to load data from a table you created earlier. If you don't want to keep that first table then perhaps you can do a preceding load instead:

MyResultTable:

LOAD *, B+C as D; \\ this is the preceding load that uses the data from the next load statement as its source

LOAD A, B, C FROM MyTable;

Re: Date Format -Urgent

Wed, 15 Apr 2015 12:19:51 GMT

Hi,

Your apporach is like below,

OldTable:

Load *

from source;

New_Table:

LOAD

Key,

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

resident OldTable GROUP BY Key;

*Post your qvd & app on my mailid.

Re: Date Format -Urgent

krishna20 — Wed, 15 Apr 2015 13:25:57 GMT

Hi Gysbert,

Let me try as per your suggestion and let you know the result.

Re: Date Format -Urgent

krishna20 — Thu, 16 Apr 2015 04:27:26 GMT

Hi Gysbert ,

I tried in both ways Preceding load and Resident concept .But, Same error it raising.I don't know where i'm doing the mistake.Please Identify the mistake in attached sample app.

Re: Date Format -Urgent

krishna20 — Thu, 16 Apr 2015 06:07:57 GMT

Hi Friends,

Any Idea on this.Please suggest me.

Re: Date Format -Urgent

Thu, 16 Apr 2015 09:48:36 GMT

Hi Krishna,

Hope your issue will be resloved.

PFA,

Thanks

Re: Date Format -Urgent

krishna20 — Thu, 16 Apr 2015 09:55:42 GMT

Thank You all !!!

Regards

Krishna