Date format

[#1]

Hi all!

I've tried to make a simple sales/year chart but I'm struggling with the dates...

I get my data from an odbc and the date format is YYYYMMDD (e.g 20120213). When I try to change the number format settings to dates the values are just nonsense...

What should I do in order to get the chart so that it would show sales/years then months and days?

Cheers!

[jagan · Accepted solution]

Hi,

Use the following example of Load statement

LOAD

     Date,

     Year(Date) AS Year,

     Month(Date) AS Month,

     Day(Date) AS Day

     '

     '

     ';

LOAD

     Date(Date#(DateField, 'YYYYMMDD')) AS Date

'

'

'

'

FROM ABC.txt;    

In the above script you can arrive the Year, Month, Day and Date in valid format.  By using Month, Year and Day dimensions you can create the charts.

Regards,

Jagan.

[CELAMBARASAN]

Hi,

     Use expression like this

     Num(Date#(YourDateField,'YYYYMMDD'))

Hope it helps

Celambarasan

[#4]

Hey Jagan!

Thanks for the quick reply!

I did not quite get this part

LOAD

     Date(Date#(DateField, 'YYYYMMDD')) AS Date

In my script I have

          ODBC connect to...

          SQL select......

          .

          .

          From...

To which part should I put that? I got a few errors when I added to My date field's name is 'lstupddte'

          Date(Date#(DateField, 'YYYYMMDD')) AS Date

Thanks and regards!

Antti

[CELAMBARASAN]

Hi,

     You can use like,

     Load

           *,

           Date(Date#(DateField, 'YYYYMMDD')) AS Date;

      SQL select......

          .

          .

      From...

Instead of  * you can use the field names from the SQL Select query.

Hope it helps

Celambarasan

[jagan]

Hi,

Date and Date# are qlikview functions.  It should not be used in SQL queries.  So first load the data from SQL and then using Preceding Load change the date format and arrive the required columns.  The example of preceding load is

Load

   *,

  Date(Date#(DateField, 'YYYYMMDD')) AS Date;

SQL select......

     DateField,

        .

       .

From TableName;

Regards,

Jagan.

[#7]

Hi,

Thanks a lot! Now I got the date format in order!

[#8]

Hi,

One more question relating to this...

Now that I have the date format set up, how can I do the sales/year so that I can drill down to months and days?

Thanks a lot in advance!

[Anonymous]

Hi

          You can achieve your solution using the pivot table .

          I attached one example qv file please refer.

[Anonymous]

Hi

        Also  You can use the drill down option from chart to achieve your solution.

        See the attached file it will helpful

Regards

Ashok