Date(yyyymmddhh) Issue

[New-Qlik]

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 .

[jonathandienst]

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

[crusader_]

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

[New-Qlik]

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

[New-Qlik]

its giving me numeric output:(

[New-Qlik]

I tried this an it is generating dates like 2016-08-01 without hour

[crusader_]

ok, but why don't you apply format function additionally as I told before?

Just

Date([Date],'YourFormat')

Regards,

Andrei

[jonathandienst]

>>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

[New-Qlik]

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

[jonathandienst]

There are already two posts in this thread which show you how to express the date correctly in the load.