Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I have a column with Dates in my Data, I want further column from it which shows Month , Qtr, years. I am using below code:
[DISP_DATE],
Week ([DISP_DATE]) as [DISP_Week],
Month([DISP_DATE]) as [DISP_Month],
Year([DISP_DATE]) as [DISP_Year],
'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater],
I am able to compile code without an error , but when I try to use Week , Month , year column in my dashboard there is no value, can someone please help me in this regard, I am very new to Qlik and not sure what error I am making .
Also, DISP_DATE columns have values like 01-Sep-2016 , 03-Sep-2016 .... 02-Oct-2016
Hi,
Try like this
Data:
LOAD
*,
Week ([DISP_DATE]) as [DISP_Week],
Month([DISP_DATE]) as [DISP_Month],
Year([DISP_DATE]) as [DISP_Year],
'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater];
LOAD
Date(Date#([DISP_DATE], 'DD-MMM-YYYY')) AS [DISP_DATE],
*
FROM DataSource;
Regards,
Jagan.
Like Jagan pointed out, there seems to be interpretation problem. QlikView doesn't always understand your date. One way to handle this without using Date#() would be to define your date format in the environmental variable
SET ThousandSep=' ';
SET DecimalSep=',';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='# ##0,00 ₽;-# ##0,00 ₽';
SET TimeFormat='h:mm:ss';
SET DateFormat='DD-MMM-YYYY';
SET TimestampFormat='DD-MMM-YYYY h:mm:ss[.fff]';
SET MonthNames='янв;фев;мар;апр;май;июн;июл;авг;сен;окт;ноя;дек';
SET DayNames='Пн;Вт;Ср;Чт;Пт;Сб;Вс';
SET LongMonthNames='Январь;Февраль;Март;Апрель;Май;Июнь;Июль;Август;Сентябрь;Октябрь;Ноябрь;Декабрь';
SET LongDayNames='понедельник;вторник;среда;четверг;пятница;суббота;воскресенье';
SET FirstWeekDay=0;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='ru-RU';
and by doing this, you would be able to avoid the use of preceding load and your current script should work
[DISP_DATE],
Week ([DISP_DATE]) as [DISP_Week],
Month([DISP_DATE]) as [DISP_Month],
Year([DISP_DATE]) as [DISP_Year],
'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater],
But many times we run into an issue where we pull data from multiple sources with different formats, that's when Date#() in handy and it makes sense to set the environmental variable to a format which you would like to see your date in the front end of the application.
Read here to learn about Dates in QlikView
Hello Jagan, thanks for the guidance , I tried to put below code in my script, it changes the format of my data from 03-Sep-2016 to 03/08/2016 that is fine, but Week, Qtr and Year columns are still blank.
Date(Date#([DISP_DATE], 'DD/MM/YYYY')) AS [DISP_DATE],
//[DISP_DATE],
Week ([DISP_DATE]) as [DISP_Week],
Month([DISP_DATE]) as [DISP_Month],
Year([DISP_DATE]) as [DISP_Year],
'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater],
Sunny, thanks for the reply, I have made changes in environmental variable as you suggested for both Date and Time Stamp and also put Date#() function, but still my Week, Month and Qtr column is blank
Date(Date#([DISP_DATE], 'DD/MM/YYYY')) AS [DISP_DATE],
//[DISP_DATE],
Week ([DISP_DATE]) as [DISP_Week],
Month([DISP_DATE]) as [DISP_Month],
Year([DISP_DATE]) as [DISP_Year],
'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater],
Hi Ankit,
The script should work, did you noticed the second load statement(Preceding Loading) in my script. I am not sure you did the preceding loading. Please find below script highlighted in Bold text.
Data:
LOAD
*,
Week ([DISP_DATE]) as [DISP_Week],
Month([DISP_DATE]) as [DISP_Month],
Year([DISP_DATE]) as [DISP_Year],
'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater];
LOAD
Date(Date#([DISP_DATE], 'DD-MMM-YYYY')) AS [DISP_DATE], // Formatting the date
*
FROM DataSource;
Regards,
Jagan.
Hi Jagan, I am loading the data from single excel worksheet, pasting the entire script below for your reference so that you can see whats wrong I am doing
Thx
Ankit
[Export Worksheet]:
LOAD
[REGION_DESC],
[TERRITORY_DESC],
[MD_CUST_ID],
[MD_CMID],
//[MD_FIRST_NAME],
//[MD_LAST_NAME],
[MD_FIRST_NAME]&' '&[MD_LAST_NAME] as [MD_NAME],
[PATIENT_CMID],
[PATIENT_FIRST_NAME]&' '&[PATIENT_LAST_NAME] as [PATIENT_NAME],
[ABC_ID],
[PATIENT_INITIALS],
[PATIENT_BIRTH_DATE],
[PATIENT_GENDER],
[PATIENT_DISPOSITION],
[REFERRAL_DATE],
[GRADUATION_DATE],
[ASAP_DISPOSITION],
//[DISP_DATE], [ This is the Original column from Excel]
Week ([DISP_DATE]) as [DISP_Week],
Month([DISP_DATE]) as [DISP_Month],
Year([DISP_DATE]) as [DISP_Year],
'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater],
Date(Date#([DISP_DATE], 'DD/MM/YYYY')) AS [DISP_DATE],
[DISP_AGE],
[DISP_REASON],
[SR_STATUS],
[SR_SOURCE],
[PROCUREMENT],
[AVONEX_SPP],
[DECISION_MAKER],
[TREATMENT_CHOICE],
[ABM_ALERT_CODE],
[MD_OPT_OUT],
[ACCESS_ENROLLED_FLAG],
[CURR_THERAPY],
[CURR_THERAPY_CONFIRM_DT],
[PREV_THERAPY],
[INS_PRVDR1_CMID],
[INS_PRVDR1_NAME],
[INS_PRVDR1_PHONE],
[INS_PRVDR2_CMID],
[INS_PRVDR2_NAME],
[INS_PRVDR2_PHONE],
[ENROLLED_ATS_FLAG],
[SHIP_DATE],
[SA_SR_ID],
[SOURCE_SR_ROW_ID],
[TRAINING_REQUIRED],
[TRAIN_BY_NURSE],
[REFUSED_TRAINING_FLG],
[COPAY_STATUS],
[ZIN_REMS_ID],
[PATIENT_REMS_STATUS],
[PATIENT_REMS_STATUS_DATE],
[MD_REMS_STATUS],
[MD_REMS_STATUS_DATE],
[JOIN_TERR],
[TERR_DESC]
FROM [lib://AttachedFiles/disp_export.xlsx]
(ooxml, embedded labels, table is [Export Worksheet]);
Hi,
Try like this
[Export Worksheet]:
LOAD
*,
Week ([DISP_DATE]) as [DISP_Week],
Month([DISP_DATE]) as [DISP_Month],
Year([DISP_DATE]) as [DISP_Year],
'Q-' & Ceil(Month([DISP_DATE])/3) as [DISP_Quater];
LOAD
[REGION_DESC],
[TERRITORY_DESC],
[MD_CUST_ID],
[MD_CMID],
//[MD_FIRST_NAME],
//[MD_LAST_NAME],
[MD_FIRST_NAME]&' '&[MD_LAST_NAME] as [MD_NAME],
[PATIENT_CMID],
[PATIENT_FIRST_NAME]&' '&[PATIENT_LAST_NAME] as [PATIENT_NAME],
[ABC_ID],
[PATIENT_INITIALS],
[PATIENT_BIRTH_DATE],
[PATIENT_GENDER],
[PATIENT_DISPOSITION],
[REFERRAL_DATE],
[GRADUATION_DATE],
[ASAP_DISPOSITION],
//[DISP_DATE], [ This is the Original column from Excel]
Date(Date#([DISP_DATE], 'DD/MM/YYYY')) AS [DISP_DATE],
[DISP_AGE],
[DISP_REASON],
[SR_STATUS],
[SR_SOURCE],
[PROCUREMENT],
[AVONEX_SPP],
[DECISION_MAKER],
[TREATMENT_CHOICE],
[ABM_ALERT_CODE],
[MD_OPT_OUT],
[ACCESS_ENROLLED_FLAG],
[CURR_THERAPY],
[CURR_THERAPY_CONFIRM_DT],
[PREV_THERAPY],
[INS_PRVDR1_CMID],
[INS_PRVDR1_NAME],
[INS_PRVDR1_PHONE],
[INS_PRVDR2_CMID],
[INS_PRVDR2_NAME],
[INS_PRVDR2_PHONE],
[ENROLLED_ATS_FLAG],
[SHIP_DATE],
[SA_SR_ID],
[SOURCE_SR_ROW_ID],
[TRAINING_REQUIRED],
[TRAIN_BY_NURSE],
[REFUSED_TRAINING_FLG],
[COPAY_STATUS],
[ZIN_REMS_ID],
[PATIENT_REMS_STATUS],
[PATIENT_REMS_STATUS_DATE],
[MD_REMS_STATUS],
[MD_REMS_STATUS_DATE],
[JOIN_TERR],
[TERR_DESC]
FROM [lib://AttachedFiles/disp_export.xlsx]
(ooxml, embedded labels, table is [Export Worksheet]);
Is you date in DD/MM/YYYY format or DD-MMM-YYYY? You are using DD/MM/YYYY for DISP_DATE?
Date(Date#([DISP_DATE], 'DD/MM/YYYY')) AS [DISP_DATE],
Hi,
Try this one
[Export Worksheet]:
LOAD
[REGION_DESC],
[TERRITORY_DESC],
[MD_CUST_ID],
[MD_CMID],
//[MD_FIRST_NAME],
//[MD_LAST_NAME],
[MD_FIRST_NAME]&' '&[MD_LAST_NAME] as [MD_NAME],
[PATIENT_CMID],
[PATIENT_FIRST_NAME]&' '&[PATIENT_LAST_NAME] as [PATIENT_NAME],
[ABC_ID],
[PATIENT_INITIALS],
[PATIENT_BIRTH_DATE],
[PATIENT_GENDER],
[PATIENT_DISPOSITION],
[REFERRAL_DATE],
[GRADUATION_DATE],
[ASAP_DISPOSITION],
//[DISP_DATE], [ This is the Original column from Excel]
Week (date(Date#([DISP_DATE], 'DD-MMM-YYYY'))) as [DISP_Week],
Month(date(Date#([DISP_DATE], 'DD-MMM-YYYY'))) as [DISP_Month],
Year(date(Date#([DISP_DATE], 'DD-MMM-YYYY'))) as [DISP_Year],
'Q-' & Ceil(Month(date(Date#([DISP_DATE], 'DD-MMM-YYYY'))/3) as [DISP_Quater],
date(Date#([DISP_DATE], 'DD-MMM-YYYY')) AS [DISP_DATE],
[DISP_AGE],
[DISP_REASON],
[SR_STATUS],
[SR_SOURCE],
[PROCUREMENT],
[AVONEX_SPP],
[DECISION_MAKER],
[TREATMENT_CHOICE],
[ABM_ALERT_CODE],
[MD_OPT_OUT],
[ACCESS_ENROLLED_FLAG],
[CURR_THERAPY],
[CURR_THERAPY_CONFIRM_DT],
[PREV_THERAPY],
[INS_PRVDR1_CMID],
[INS_PRVDR1_NAME],
[INS_PRVDR1_PHONE],
[INS_PRVDR2_CMID],
[INS_PRVDR2_NAME],
[INS_PRVDR2_PHONE],
[ENROLLED_ATS_FLAG],
[SHIP_DATE],
[SA_SR_ID],
[SOURCE_SR_ROW_ID],
[TRAINING_REQUIRED],
[TRAIN_BY_NURSE],
[REFUSED_TRAINING_FLG],
[COPAY_STATUS],
[ZIN_REMS_ID],
[PATIENT_REMS_STATUS],
[PATIENT_REMS_STATUS_DATE],
[MD_REMS_STATUS],
[MD_REMS_STATUS_DATE],
[JOIN_TERR],
[TERR_DESC]
FROM [lib://AttachedFiles/disp_export.xlsx]
(ooxml, embedded labels, table is [Export Worksheet]);