topic Re: Date Conversion in QlikView

Date Conversion

Mon, 28 Oct 2013 15:27:54 GMT

I am reading the csv file which has the date column. Loading this data into the script. When loading i am getting the Year, Month from this date. But the values comes out as NULL.

The actual date fields has value. But when converting the values are not getting populated. I did the Date# conversion also.

Can someone help on what could be the issue.

Thanks

Ramesh

Re: Date Conversion

shree909 — Mon, 28 Oct 2013 15:31:51 GMT

Hi could you please post a sample of file.

or the data column values from the csv file .

that will be easy to figure it out..

Mostly u need to use the Date(Date#( 'ur date filed column,/'MM/DD/YYYY'),' to convert to the desired format') as Date

This should solve ur issue or load a sample of data...

Re: Date Conversion

tresB — Mon, 28 Oct 2013 15:34:19 GMT

May be your field reference is not being correct. Try 'File Wizard' to load the data and see if you can find the data at the right place.

Re: Date Conversion

Mon, 28 Oct 2013 15:38:19 GMT

Hi Ana

Here is the script

ASW_MAIN:

LOAD

BillingPeriondStartDate, This has value 9/1/2013 00:00

date#(BillingPeriodStartDate,'M/D/YYYY') as billstartdate, This is also getting the value as 9/1/2013
Year(BillingPeriodStartDate) as BillingYear,This has no value
Month(BillingPeriodStartDate) as BillingMonth, This has no value
BillingPeriodEndDate, This has value 9/30/2013 23:59

LOAD
Min(BillingPeriodStartDate) as MinDate,
Max(BillingPeriodEndDate) as MaxDate
Resident AWS_MAIN;

Both the MinDate and MaxDate are getting null values. I checked the columns for null values and none of them have null values.

Thanks

Ramesh

Re: Date Conversion

Clever_Anjos — Mon, 28 Oct 2013 15:38:43 GMT

It hard to think a reason without knowing your data, please post a line of your csv file

Re: Date Conversion

shree909 — Mon, 28 Oct 2013 15:58:19 GMT

Hi Use this

This should work

Year(Floor(date#(BillingPeriodStartDate,'M/D/YYYY')))as BillingYear,
Month(Floor(date#(BillingPeriodStartDate,'M/D/YYYY'))) as BillingMonth,

If this doesn't work load a sample file with data or csv file..

Re: Date Conversion

Mon, 28 Oct 2013 17:06:20 GMT

Here is the data

InvoiceID,PayerAccountId,LinkedAccountId,RecordType,RecordID,BillingPeriodStartDate,BillingPeriodEndDate

234,22312314,1231200001,LinkedLineItem,36012341200,9/1/2013 0:00,9/30/2013 23:59

Thanks

Ramesh

Re: Re: Date Conversion

Clever_Anjos — Mon, 28 Oct 2013 17:14:33 GMT

Converting to a date

LOAD

InvoiceID,

PayerAccountId,

LinkedAccountId,

RecordType,

RecordID,

date#(BillingPeriodStartDate,'M/D/YYYY h:mm') as BillingPeriodStartDate,

date#(BillingPeriodEndDate,'M/D/YYYY h:mm') as BillingPeriodEndDate

INLINE [

InvoiceID,PayerAccountId,LinkedAccountId,RecordType,RecordID,BillingPeriodStartDate,BillingPeriodEndDate

234,22312314,1231200001,LinkedLineItem,36012341200,9/1/2013 0:00,9/30/2013 23:59

];