Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

leale1997
New Contributor III

Formating Date in Load statement from excel file.

Good day!

I have an excel file with several Date fields that QV can not recognize as a date and I can't figure out why.  I have tried the following on the load script:

Date([Field Name],'MM/DD/YYYY') as DateCreated,

(the result is it doesn't load any values for the field at all)

I have also tried using the 'Number' tab in the 'Properties' box after import.

(The result of this is it doesn't visually change the format of the date field at all).

Any input or advise is greatly appreciated.  I am attaching a sample from the excel file I am trying to load.

1 Solution

Accepted Solutions
vishsaggi
Esteemed Contributor III

Re: Formating Date in Load statement from excel file.

Can you share your script you are running. It works for me.

Try below:

DateField:

LOAD [Record ID],

     Project,

     [Originating Site],

     Supply,

     Date(Date#([Date Created], 'MM/DD/YYYY'),'MM/DD/YYYY') AS DateCreated,

     [Date Due],

     DateClosed

FROM

(ooxml, embedded labels, table is PQC);

9 Replies
vishsaggi
Esteemed Contributor III

Re: Formating Date in Load statement from excel file.

Can you share your script you are running. It works for me.

Try below:

DateField:

LOAD [Record ID],

     Project,

     [Originating Site],

     Supply,

     Date(Date#([Date Created], 'MM/DD/YYYY'),'MM/DD/YYYY') AS DateCreated,

     [Date Due],

     DateClosed

FROM

(ooxml, embedded labels, table is PQC);

leale1997
New Contributor III

Re: Formating Date in Load statement from excel file.

Wow so simple!

You might be my new best friend

Many thanks!

vishsaggi
Esteemed Contributor III

Re: Formating Date in Load statement from excel file.

Haha! No problem.

leale1997
New Contributor III

Re: Formating Date in Load statement from excel file.

Might I bother you with one follow-up question?

I am creating a calendar in the load statement using one of the fields I just formatted according to the code you created.  It looks like this:

TempCalendar: 
LOAD 
               $(varMinDate) + Iterno()-1 As Num, 
               Date($(varMinDate) + IterNo() - 1) as TempDate 
               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 
 
DateClosed_MasterCalendar: 
Load 
               TempDate AS DateClosed, 
               week(TempDate) As ClosedWeek, 
               Year(TempDate) As ClosedYear, 
               Month(TempDate) As ClosedMonth, 
               Day(TempDate) As ClosedDay, 
               YeartoDate(TempDate)*-1 as ClosedCurYTDFlag, 
               YeartoDate(TempDate,-1)*-1 as ClosedLastYTDFlag, 
               inyear(TempDate, Monthstart($(varMaxDate)),-1) as ClosedRC12, 
               date(monthstart(TempDate), 'MMM-YYYY') as ClosedMonthYear, 
               ApplyMap('QuartersMap', month(TempDate), Null()) as ClosedQuarter, 
               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as ClosedWeekYear, 
               WeekDay(TempDate) as ClosedWeekDay 
Resident TempCalendar 
Order By TempDate ASC; 
Drop Table TempCalendar; 

Every field is being created EXCEPT  the 'ClosedQuarter' field.  It is coming up blank.  Very strange?  Any ideas?

shiveshsingh
Honored Contributor

Re: Formating Date in Load statement from excel file.

What are the values in your mapping table?

QuartersMap

leale1997
New Contributor III

Re: Formating Date in Load statement from excel file.

It's the same data sample I attached to the last question.  Is that what you are asking?  I just gave that data load a name in the QV file and called it 'All Project Table'

vishsaggi
Esteemed Contributor III

Re: Formating Date in Load statement from excel file.

No in your script you should have a Load statement starting like

QuarterMap:

Mapping Load ...

leale1997
New Contributor III

Re: Formating Date in Load statement from excel file.

Ok.  turns out it wasn't in the script i copied you on because i forgot to put it in the script and that is in fact the problem.....

No wonder i couldn't get it to work. 

vishsaggi
Esteemed Contributor III

Re: Formating Date in Load statement from excel file.

No worries. Let us know if it worked.

Community Browser