topic Re: Date(yyyymmddhh) Issue in QlikView

Date(yyyymmddhh) Issue

New-Qlik — Wed, 25 Nov 2020 16:16:04 GMT

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

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 .

Re: Date(yyyymmddhh) Issue

jonathandienst — Tue, 16 Aug 2016 07:23:04 GMT

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

Re: Date(yyyymmddhh) Issue

crusader_ — Tue, 16 Aug 2016 07:29:37 GMT

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

Re: Date(yyyymmddhh) Issue

New-Qlik — Tue, 16 Aug 2016 07:37:19 GMT

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

Re: Date(yyyymmddhh) Issue

New-Qlik — Tue, 16 Aug 2016 09:22:37 GMT

its giving me numeric output:(

Re: Date(yyyymmddhh) Issue

New-Qlik — Tue, 16 Aug 2016 09:23:41 GMT

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

Re: Date(yyyymmddhh) Issue

crusader_ — Tue, 16 Aug 2016 11:51:15 GMT

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

Just

Date([Date],'YourFormat')

Regards,

Andrei

Re: Date(yyyymmddhh) Issue

jonathandienst — Wed, 17 Aug 2016 09:56:36 GMT

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

Re: Date(yyyymmddhh) Issue

New-Qlik — Thu, 18 Aug 2016 19:09:49 GMT

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

Re: Date(yyyymmddhh) Issue

jonathandienst — Fri, 19 Aug 2016 05:03:04 GMT

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