Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
leale1997
Contributor III
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
Champion III
Champion III

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);

View solution in original post

9 Replies
vishsaggi
Champion III
Champion III

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
Contributor III
Contributor III
Author

Wow so simple!

You might be my new best friend

Many thanks!

vishsaggi
Champion III
Champion III

Haha! No problem.

leale1997
Contributor III
Contributor III
Author

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

What are the values in your mapping table?

QuartersMap

leale1997
Contributor III
Contributor III
Author

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
Champion III
Champion III

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

QuarterMap:

Mapping Load ...

leale1997
Contributor III
Contributor III
Author

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
Champion III
Champion III

No worries. Let us know if it worked.