topic Re: Problem with date format or function in QlikView

Problem with date format or function

Fri, 16 May 2014 13:36:18 GMT

Hi Everyone,

I`m a beginer in QV and I work on Personal edition. I have a problem with Date. Enclosed you will find my source file. Simply I would like to do functions below:

FileBaseName() as Source,
Date(Date#([],'YYYY-MM-DD HH:MM:SS'),'DD-MM-YYYY') as Date,
   Year(Date(Date#([],'YYYY-MM-DD HH:MM:SS'),'DD-MM-YYYY')) as Year,
       Month(Date(Date#([],'YYYY-MM-DD HH:MM:SS'),'DD-MM-YYYY')) as Month,
        Day(Date(Date#([],'YYYY-MM-DD HH:MM:SS'),'DD-MM-YYYY')) as Day,
           'Q' & CEIL(num(Month(Date(Date#([Data wysłania],'YYYY-MM-DD HH:MM:SS'),'DD-MM-YYYY')))/3) as Quarter

but they don't work properly on my source file. Do you have some ideas how to solve this problem?

Re: Problem with date format or function

Anonymous — Fri, 16 May 2014 13:46:44 GMT

Try like below

Date(left(Date#,10) as Date etc...

Re: Problem with date format or function

Anonymous — Fri, 16 May 2014 13:50:08 GMT

The format is case sensitive. Use 'YYYY-MM-DD hh:mm:ss'. And, looks like you don't need time part.

Next, see if the Date is created correctly:

Date:

LOAD DISTINCT

Date(floor(Date#([],'YYYY-MM-DD HH:MM:SS'),'DD-MM-YYYY')) as Date

FROM...

If it is, load the rest:

LEFT JOIN (Date) LOAD

Year(Date) as Year,

....

RESIDENT Date;

Re: Re: Problem with date format or function

Mon, 19 May 2014 07:13:37 GMT

Thanks you for your reply but, unfotunately it still doesn't work I supposed that problem is in the date format, but I can't see any difference in the excel. As you can see in attached picture, after basic function like this

Date#([@1],'YYYY-MM-DD HH:MM:SS') as @1 my data looks like that. I have tried many combinations, but I didn't solved this problem.

Re: Re: Re: Problem with date format or function

ashfaq_haseeb — Mon, 19 May 2014 07:38:52 GMT

Hi, may be like this.

Regards

ASHFAQ

Re: Problem with date format or function

Mon, 19 May 2014 08:10:39 GMT

Hi Ashfaq,

could you send my youre script on txt format? I`ve problem with open qvw files, cause I`ve Personal Edition

Thanks you in advance

Ewelina

Re: Problem with date format or function

ashfaq_haseeb — Mon, 19 May 2014 08:19:59 GMT

Hi,

Please find below code.

Table:

LOAD @1,

date(makedate(left(@1,4),mid(@1,6,2),mid(@1,9,2)),'YYYY-MM-DD') as Date

FROM

LB18_20140513_100839.xls

(biff, no labels, table is Sheet1$);

Regards

ASHFAQ

Re: Problem with date format or function

Mon, 19 May 2014 12:09:51 GMT

Thank you very much my script (below) is working, but is there possible to save orginal date?

Dates:

LOAD date(makedate(left(@1,4),mid(@1,6,2),mid(@1,9,2)),'YYYY-MM-DD HH:MM:SS') as Date1,

Date(@1) as Date,

Year(@1) as Year,

Month(@1) as Month,

Day(@1) as Day,

'Q' & CEIL(num(Month(Date([@1]))/3)) as Quarter

FROM

LB18_20140513_100839.xls

(biff, no labels, table is Sheet1$);

Re: Re: Problem with date format or function

ashfaq_haseeb — Mon, 19 May 2014 12:14:54 GMT

Hi,

Below code must work for you.

Main:
LOAD @1 as OriginalDate,
     date(makedate(left(@1,4),mid(@1,6,2),mid(@1,9,2)),'YYYY-MM-DD') as Date
FROM
LB18_20140513_100839.xls
(biff, no labels, table is Sheet1$);

left join(Main)

load Date,
  
     Year(Date) as Year,
     Month(Date) as Month,
      Day(Date) as Day,
     'Q' & CEIL(num(Month(Date(Date))/3)) as Quarter
Resident Main;

Regards

ASHFAQ

Re: Problem with date format or function

Thu, 22 May 2014 06:56:01 GMT

Thanks a lot for your help

Re: Problem with date format or function

Eric_Tay — Tue, 26 Aug 2014 04:56:50 GMT

This works! Anyone can explain why this works?

Left() returns string, but when we wrap it with Date() that takes numeric format of data, it works.