topic Re: urgent help needed in QlikView

urgent help needed

Mon, 26 May 2014 14:38:37 GMT

Hi All,

I'm having a csv file which I have used in load script. It is having 2 date fields named contract_start_date and contract_end_date.

I'm having a chart where I want to do the following 2 things

1) add one month to all the values of contract_start_date field (tried using AddMonths( CONTRACT_START_DATE,1)) and

2) find the difference between contract_end_date and today() in months (tried using CONTRACT_END_DATE-Today())

but both of them are not working. Can anyone please help me to solve this.

Attached is my qlikview file.

Thanks in advance!!!

Re: urgent help needed

Marcio_Campestrini — Mon, 26 May 2014 14:45:44 GMT

Hi.

Try to format dates during the load using date function. If you need the hour, split in two fields.

Re: urgent help needed

Mon, 26 May 2014 14:50:24 GMT

try ....

date(field_name) as date_field_name

day(field_name) as day_field_name

week(field_name) as week_field_name

month(field_name) as month_field_name

year(field_name) as year_field_name

....

Re: urgent help needed

Mon, 26 May 2014 14:50:58 GMT

are you sure your dates are under date format and not looking as dates

chris

Re: urgent help needed

Mon, 26 May 2014 14:52:15 GMT

Hi Kavita,

AddMonths will do the trick. I think the problem is when you pull the data in the data isn't in a date format.

As mentioned by Marcio try changing the field to a date format by doing a date in the script. One this has been resolved I'm sure you will then be able to do the calculations you need to on the date fields.

Regards,
Marius

Re: urgent help needed

Tue, 27 May 2014 04:22:59 GMT

Hi Marcio,

I tried formatting the date using date functions but its not working.

Re: urgent help needed

tresB — Tue, 27 May 2014 04:41:11 GMT

Date format is the issue. The date fields are not really dates, rather strings. You have to get the proper timestamp using date parsing function - Date#(). I have done for the last expression in the attached qvw. It would be better if you load the dates using Date#(), like:

LOAD BILL_PROFILE_ID,

Date#(CONTRACT_START_DATE, 'DD-MMM-YY hh.mm.ss.ffffff tt') as CONTRACT_START_DATE

CONTRACT_TERMS,

Date#(CONTRACT_END_DATE, 'DD-MM-YYYY') as CONTRACT_END_DATE

From <>;

Re: urgent help needed

Tue, 27 May 2014 04:44:28 GMT

TRY THIS

For 1st Query :-

this will give the result in numeric format

=(DATE(date#(CONTRACT_END_DATE,'DD-MM-YYYY'),'M/DD/YYYY')-Today())

this will give the result in date format

=DATE(DATE(date#(CONTRACT_END_DATE,'DD-MM-YYYY'),'M/DD/YYYY')-Today())

For 2nd Query :-

=DATE(DATE#(SubField(CONTRACT_START_DATE,' ',1),'DD-MMM-YY'),'M/DD/YYYY')

Re: urgent help needed

Tue, 27 May 2014 05:41:10 GMT

Hi.

TRY THIS:

For 1st query:_

= AddMonths( Date#(CONTRACT_START_DATE,'DD-MMM-YY hh.mm.ssssss tt'),1)

For 2nd Query :-

this will give the result in numeric format

=(DATE(date#(CONTRACT_END_DATE,'DD-MM-YYYY'),'M/DD/YYYY')-Today())

this will give the result in date format

=DATE(DATE(date#(CONTRACT_END_DATE,'DD-MM-YYYY'),'M/DD/YYYY')-Today())

Re: urgent help needed

Mon, 02 Jun 2014 11:46:45 GMT

Thanks a lot tresesco, Its working now