Failing to Load Data as Date

[Seiya]

Hi there, new to Qlik and currently trying to get a date field actually load as date.

At the moment I think it's loading it straight as text.

When clicking the edit button there is no date icon next to the field, in fact there isn't any icon except a single geo icon next to a field when has nothing to do with geo data but that doesn't matter.

I've googled and seen many uses of

Date(fieldname,'dd-mm-yyy') examples but there's never any full context, where exactly does this go?

The only logical area I've found is in the Data Load Editor, after unlocking the "auto generate section" I edit something like as follows:

[bi_tools_bsj_data]:
LOAD 
....
	Date([rcom_date],'dd-mmm-yyyy'),
....

And multiple variations there of as posted in multiple posts.

What happens?

Nothing.

The field just returns blank once it's reloaded.

Any other answers to help me out would be greatly appreciated, thanks.

[jonathandienst · Accepted solution]

Two things here.

1. To interpret a date that is not detected by QS, use Date#, possibly combined with Date():

Date(Date#([rcom_date], 'yyyy-MM-dd'), 'dd-MM-yyyy')

This will interpret the string (Date#()) in yyyy-MM-dd format and output the date (Date()) as a numeric in dd-MM-yyyy format.

2. The format character for month is uppercase M. Lowercase m stands for minutes.

[FakeJupiter · Accepted solution]

Hi!

Try using date#() instead of date() and tell me how it works.

If it doesn't help, please clarify:

• When you use the date field in a chart or table, does it return date as text or null values?
• If you load date data from Excel, please set a random cell that contains date to format "general" and paste it here.

Sincerely,
Ray

[Seiya]

Thanks date# works, had seen that before but they left out the fact that months had to be a capital 'M'.

Such small things left out makes such a big difference.

The output now is only half way there though unfortunately.

I wanted it to display and group into months e.g 'MMM-yy'

but now it just individual dates with the days missing, can we group them so all days in the month and year are one data point (like an excel pivot table).

[FakeJupiter]

What do you mean days missing? Do you want the data to be grouped by year, month, and weekday?

Please provide an example of  how the date looks now versus how do you want it to look.

[Seiya]

As an example, what was previously

1-Apr-2017

2-Apr-2017

3-Apr-2017

is now just

Apr-17

Apr-17

Apr-17

I want them all grouped up in a month so I only one month per year:

Jan-2017

Feb-2017

Mar-2017

Apr-2017

etc...

[jonathandienst · Accepted solution]

You need to change the resolution of the value being formatted:

Date(MonthStart(Date#([rcom_date], 'yyyy-MM-dd')), 'MMM-yyyy'),

Otherwise you will have multiple values that look the same, even though they are not

[Seiya]

Fantastic, looks good so far.  Much appreciated.

Now to mosy on until I get stuck at another point.

Give me 10 minutes 😅