Not getting years and month

[sunil-kumar5]

Hi Community,

In my scrip I am using below method to create year and month for my table but now sure as why I am getting blank in years and month column while checking in data modeling.

Created_Date,
YEAR(Created_Date) as TYear,
MONTH(Created_Date) as TMonth,

Is there any idea? I did apply the same way before it worked always but not for now.

[Kushal_Chawda]

That means your dates are not in proper date format

try below. Assuming your date values are in format 'DD-MM-YYYY' you can change the format according to actual format of date value

YEAR(date(date#(Created_Date,'DD-MM-YYYY'))) as TYear,
MONTH(date(date#(Created_Date,'DD-MM-YYYY'))) as TMonth

[sunil-kumar5]

Hi,

My date format is in DD/MM/YYYY H:MM format. I have tried with chaning the formating but it did'work.

Thanks

[Taoufiq_Zarra]

if not you can always do :

Makedate(left(subfield(Created_Date,'/',3),4),subfield(Created_Date,'/',2),subfield(Created_Date,'/',1)) as Created_Date,
Year(Makedate(left(subfield(Created_Date,'/',3),4),subfield(Created_Date,'/',2),subfield(Created_Date,'/',1))) as TYear,
MONTH(Makedate(left(subfield(Created_Date,'/',3),4),subfield(Created_Date,'/',2),subfield(Created_Date,'/',1))) as TMonth

[Kushal_Chawda]

try below

YEAR(floor(timestamp#(Created_Date,'DD/MM/YYYY hh:mm'))) as TYear,
MONTH(floor(timestamp#(Created_Date,'DD/MM/YYYY hh:mm'))) as TMonth

[Kushal_Chawda]

or simply change the set variable in main tab as below

SET TimestampFormat='DD/MM/YYYY hh:mm';

then you can use your current logic to get the month and year

[sunil-kumar5]

Hi All,

Still same problem, I have applied all the above suggestion. I am sharing the further details below and if anyone can please look into it.

Inbuilt formatinng after loading the data

SET TimeFormat='h:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm';

Table first which has the dates:

JOINING_DATE,
YEAR(JOINING_DATE) as JYear,
'Q-'&Ceil(Month(JOINING_DATE)/3) as JQuarter,
MONTH(JOINING_DATE) as JMonth,
'W-'&Week(JOINING_DATE) as JWeek,
WeekDay(JOINING_DATE) as JWeekDay,
Day(JOINING_DATE) as JDay,
CONFIRMATION_DATE,
LEAVING_DATE,
YEAR(LEAVING_DATE) as LYear,
MONTH(LEAVING_DATE) as LMonth,

Now I am placing the data in other table that is TMS:

TMS:
LOAD
COMPANY_CODE,
COMPANY_NAME,
ticket_No,
ticket_subject,
ticket_message,
ticket_CreatedBy,
ticket_CreatedBy_Department,
ticket_assignedto,
ticket_Assignedto_department,
Created_Date, (Here I need to place year and month as stated above)

Please look into this

[Kushal_Chawda]

Can you send a snapshot of CreatedDate values?

[Anonymous]

Did you check if Created_Date is being loaded as a string? If yes you have to use MakeDate(YYYY [ , MM [ , DD ] ]) function to convert the string into a date?

https://help.qlik.com/en-US/sense/June2020/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeFun...

[sunil-kumar5]

here is an attachment