Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
Try like below
Date(left(Date#,10) as Date etc...
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;
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.
Hi, may be like this.
Regards
ASHFAQ
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
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
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$);
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
Thanks a lot for your help