topic Re: Differences in Dates with SQL and LOAD in QlikView

Differences in Dates with SQL and LOAD

Fri, 10 Jan 2014 19:46:39 GMT

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?

Re: Differences in Dates with SQL and LOAD

Nicole-Smith — Fri, 10 Jan 2014 19:50:15 GMT

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.

Re: Differences in Dates with SQL and LOAD

Colin-Albert — Fri, 10 Jan 2014 21:20:25 GMT

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

Re: Differences in Dates with SQL and LOAD

Mon, 13 Jan 2014 17:50:30 GMT

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

Re: Differences in Dates with SQL and LOAD

Nicole-Smith — Mon, 13 Jan 2014 18:00:39 GMT

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.

Re: Differences in Dates with SQL and LOAD

Mon, 13 Jan 2014 18:12:19 GMT

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

Re: Differences in Dates with SQL and LOAD

Colin-Albert — Mon, 13 Jan 2014 18:19:10 GMT

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.

Re: Differences in Dates with SQL and LOAD

Mon, 13 Jan 2014 19:11:18 GMT

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

Re: Differences in Dates with SQL and LOAD

Colin-Albert — Mon, 13 Jan 2014 20:08:53 GMT

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

Re: Differences in Dates with SQL and LOAD

Mon, 13 Jan 2014 20:28:44 GMT

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.

Re: Differences in Dates with SQL and LOAD

Colin-Albert — Mon, 13 Jan 2014 20:48:33 GMT

The reason you are seeing each day is that date(date_field, 'YYYY-MM) will alter the text component but leave the numeric part of the dual field unchanged.

Try monthstart(date_field) instead.

Re: Differences in Dates with SQL and LOAD

Mon, 13 Jan 2014 21:39:59 GMT

I tried this

DATE(MONTHSTART(CreatedDate), 'YYYY-MM')

and this gave me the output for each month on the chart.

The only problem now is that the monthly totals dont match up with the previous chart and are for the most part less than the prevvious chart.

Thanks for all the help and advice...as always I've learned more about QV than I had bargained for.

Re: Differences in Dates with SQL and LOAD

Mon, 13 Jan 2014 21:59:05 GMT

I figured out what I'd done to get the totals wrong...wasn't anything to do with the dates...was just something else that got changed while trying to figure why this wasn't working and its corrected now.

Thanks again