topic Re: Formating Date in Load statement from excel file. in QlikView

Formating Date in Load statement from excel file.

leale1997 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Formating Date in Load statement from excel file.

vishsaggi — Wed, 16 May 2018 15:55:58 GMT

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

Re: Formating Date in Load statement from excel file.

leale1997 — Wed, 16 May 2018 16:01:06 GMT

Wow so simple!

You might be my new best friend

Many thanks!

Re: Formating Date in Load statement from excel file.

vishsaggi — Wed, 16 May 2018 16:05:16 GMT

Haha! No problem.

Re: Formating Date in Load statement from excel file.

leale1997 — Wed, 16 May 2018 16:25:42 GMT

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?

Re: Formating Date in Load statement from excel file.

shiveshsingh — Wed, 16 May 2018 16:28:45 GMT

What are the values in your mapping table?

QuartersMap

Re: Formating Date in Load statement from excel file.

leale1997 — Wed, 16 May 2018 16:32:23 GMT

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'

Re: Formating Date in Load statement from excel file.

vishsaggi — Wed, 16 May 2018 17:31:33 GMT

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

QuarterMap:

Mapping Load ...

Re: Formating Date in Load statement from excel file.

leale1997 — Wed, 16 May 2018 17:42:45 GMT

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.

Re: Formating Date in Load statement from excel file.

vishsaggi — Wed, 16 May 2018 18:12:35 GMT

No worries. Let us know if it worked.