Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a date column in yyyymmddhh format.
I am have generated calendar using min(date) and max(date) from table
[16-08-2016 11:50] Avneet Khurana:
MinMax:
LOADMIN(Date) ASMinDate,
MAX(Date) ASMaxDate
RESIDENT Data;
LETvMinDate = NUM(LEFT(PEEK('MinDate'),8),'###0');
LETvMaxDate = NUM(LEFT(PEEK('MaxDate'),8),'###0');
FOR A = $(vMinDate) to $(vMaxDate)
LET vMinDate = $(A);
FOR B = 0 TO 23
CALMASTER:
LOAD $(vMinDate)&NUM($(B),'00') AS [Christian Full Date],
ROWNO() AS SID
AUTOGENERATE(1);
NEXT
now let suppose
mindate is = 2016072505
maxdate = 2016081500
now what calender is doing after after 2016073123 it genertaing
2016073200
2016073201
2016073202
2016073203
etc
its should move to august but its considerting date as number .
Convert these strings to proper date values when first loaded:
LOAD Date#(Date, 'yyyyMMddhh') as Date, // case significant - MM is months, mm is minutes
...
Then load the calendar like
MinMax:
LOAD Min(Date) AS MinDate,
Max(Date) AS MaxDate
RESIDENT Data;
LET vMinDate = Floor(Peek('MinDate'));
LET vMaxDate = Floor(Peek('MaxDate'));
For A = vMinDate To vMaxDate
For B = 0 To 23
CalMaster:
LOAD Date($(A) + ($(B) / 24)) AS [Christian Full Date],
RowNo() AS SID
Autogenerate(1);
Next
Next
Hi
Try below script:
MinMax:
LOAD
MIN(Date) AS MinDate,
MAX(Date) AS MaxDate
RESIDENT Data;
LET vMinDate = Num(Date#(PEEK('MinDate'),'YYYYMMDDHH'));
LET vMaxDate = Num(Date#(PEEK('MaxDate'),'YYYYMMDDHH'));
Calendar:
LOAD
$(vMinDate)+RowNo()/24-1/24 as Date
AutoGenerate ($(vMaxDate)-$(vMinDate)+1)*24;
Your Date field will contain Date+HH... so, just use correct format like Date(Date,'YYYYMMDDHH');
Hope this helps.
Regards,
Andrei
incorrect syntax error
Data:
SQL SELECT Channel,
ChristianDayDate ,
// ChristianFullDate as [Christian Full Date],
(ChristianFullDate, 'yyyyMMddhh') as [Christian Full Date],
from table
MinMax:
LOAD Min([Christian Full Date],) AS MinDate,
Max([Christian Full Date],) AS MaxDate
RESIDENT Data;
LET vMinDate = Floor(Peek('MinDate'));
LET vMaxDate = Floor(Peek('MaxDate'));
For A = vMinDate To vMaxDate
For B = 0 To 23
CalMaster:
LOAD Date($(A) + ($(B) / 24)) AS [Christian Full Date],
RowNo() AS SID
Autogenerate(1);
Next
Next
its giving me numeric output:(
I tried this an it is generating dates like 2016-08-01 without hour
ok, but why don't you apply format function additionally as I told before?
Just
Date([Date],'YourFormat')
Regards,
Andrei
>>I tried this an it is generating dates like 2016-08-01 without hour
They area formatted yyyyMMdd, so you wont see the hour. Change the format (eg yyyyMMdd hh) to show the time. Also if you look at the underlying number*, you will see the "24ths" (hours) in the fractional part.
*create a list box for this field, and set a numeric format in the Number tab
with Date(date#(start_date, 'yyyymmdd'),'dd/mm/yyyy') its working fine
but in for loop output is coming as some random date with year as 1990 1989 etc
FOR A = $(vMinDate) to $(vMaxDate)
FOR B = 0 TO 23
CALMASTER:
LOAD $(A)&NUM($(B),'00') AS [Christian Full Date],
ROWNO() AS SID
AUTOGENERATE(1);
NEXT
There are already two posts in this thread which show you how to express the date correctly in the load.