Differences in Dates with SQL and LOAD

[#1]

I have been looking at changing some of SQL Querys in to Qlikview LOADS but have an odd problem

In SQL I have this

CONVERT(VARCHAR(7), CreateDate, 120) AS CreatedDateYYYYMM

In the LOAD I have this

Date(CreateDate, 'YYYY-MM') AS CreatedDateYYYYMM

I have a chart with the CreatedDateYYYYMM field as a Dimension and this works as planned with the SQL statement i.e. it displays the number of calls for each month but when using the LOAD it displays each call separately in each month rather than display the total for the month.

The date output for each looks the same i.e. 2013-01, 2013-02 etc the only difference I can see is that in the table previewer the CONVERT one is displayed left formatted and the DATE one is right formatted.

Any ideas why this is?

[Nicole-Smith]

If it's left formatted, it's considering it as a string.  If it's right formatted, it's considering it as a number.  So for a date, you want it to be right formatted.

[Colin-Albert]

Date fields in QlikView are dual data types, stored as a number but displayed as text.

You can use date#() to convert text to date and then use date() to ensure the value is held as a dare in QlikView.

The QlikView Expression below will convert a date held as a string YYYYMMDD into a date value. You can use other format strings.

Date(date#(CreatedDate, 'YYYYMMDD')) as CreatedDate

[#4]

I thought it was probably something to do with the different internal formatting but if I use

Date(date#(CreatedDate, 'YYYY-MM')) as CreatedDate

The CreatedDate column has no values in it at all

[Nicole-Smith]

Inside the date#() you need the original formatting, and in the date() you need the output formatting.  From what I'm gathering, you want the output to be 'YYYY-MM', so you have that in the wrong spot.  You need to put the correct initial formatting within the date#():

Date(date#(CreatedDate, 'DD/MM/YYYY'), 'YYYY-MM') as CreatedDate

Replace the 'DD/MM/YYYY' with whatever your original date formatting is.

[#6]

The original date field is a date time field...would that be having an impact when its being converted.

[Colin-Albert]

It should make no difference to the format expression your data being a datetime field. Obviously applying a date format without a time component means that the time value will be lost.

In QlikView it is best practice to separate datetime fields into separate date and time fields where you want both components, as this reduces the number of unique data values, so the data can be stored much more efficiently in QlikView.

[#8]

I have been attempting to separate out the date time fields.

In this case if I chart the number value from

date#(CreatedDate, 'YYYY-MM')) as CreatedDate

It does chart each unique date/time value as a number which makes makes me think that even when it displays 2013-01 it still internally knows this is a date/time value

[Colin-Albert]

Data fields in QlikView are held as dual data types.

For date and time fields the data is stored as a number and displayed as text.

You can use text(field) and num(field) to extract the text and number values separately from a dual field.

You can manually create a dual field using dual(text, number) this can be useful when you want to sort text in a non-alphabetic sequence. e.g. Status values.

For datetime data the number is floating point, integer for the date, the decimal part is the time component.

     date(floor(datetime_value), 'DD/MM/YYYY')           will extract the date only

     time(frac(datetime_value), 'hh:mm:ss')                will extract the time component only

[#10]

That makes sense but I still end up a graph that shows a column for each day of each month rather than single columns showing the total for each month.

I tried using the floor function in the LOAD script and in the expression but the same thing occurs.

I also tried using LEFT to strip of the date part before doing a conversion.