Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Date field format to 'YYYY-MM-DD' in Qliksense script ?

Hi All ,

Please kindly assist me to get the [Claims Registered] date field to show the same way like '2019-08-28' in the qliksense script level. 

clipboard_image_0.png

I used =Date([Claim Registered],'YYYY-MM-DD') in the field but when selecting the [Claim Registered] all the condition is showing . How can I avoid this and do in script level. sample model attached.

clipboard_image_1.png

 

Found something -

The Date which is appearing like '2019/08/03' ,'2019/08/18' are not comimg from table field [Claim Registered] .not sure if it being created when doing MasterCalendar .

clipboard_image_0.png

6 Replies
Highlighted
Master III
Master III

Try like this

Date(alt(Date#([Claim Registered], 'YYYY/MM/DD'), Date#([Claim Registered], 'YYYY-MM-DD')), 'YYYY-MM-DD')

Highlighted
Creator III
Creator III

Its working but when I select the [Claim Registered] field I am getting like this- which I want to avoid .I don't to show all the condition applied to user.

clipboard_image_0.png

Highlighted
Master III
Master III

Create as a field in load script

 

Date(alt(Date#([Claim Registered], 'YYYY/MM/DD'), Date#([Claim Registered], 'YYYY-MM-DD')), 'YYYY-MM-DD') as Date

Creator III
Creator III

I tried but getting an error.

clipboard_image_0.png

Highlighted
Creator III
Creator III

Found something -

The Date which is appearing like '2019/08/03' ,'2019/08/18' are not comimg from table field [Claim Registered] .not sure if it being created when doing MasterCalendar .

clipboard_image_0.png

Highlighted
Master III
Master III

Try this

Date(alt(Date#([Claim Registered], 'YYYY/MM/DD'), Date#([Claim Registered], 'YYYY-MM-DD')), 'YYYY-MM-DD') as Date

SQL SELECT
CLAIM_KEY
,POLICY_WEAKKEY AS [POLICY_KEY]
// ,POLICY_UW_MONTH_WEAKKEY
, [SUM_INSURED] as [Sum Insured]
,[ORIGINAL_ESTIMATE] as [Original Estimate]
,[VEHICLE_MAKE]
,[INCEPTION]
,[OWN_DAMAGE_ESTIMATE]
,[THIRD_PARTY_LIABILITY_ESTIMATE]
,[CLAIM_REGISTERED] as [Claim Registered]
,[INSURER_POLICY_NO]
,CLAIM_NUMBER AS [Claim Number]
,[DOL] as "Date Of Loss"
,[DESCRIPTION_OF_LOSS] as [Description Of Loss]
,[BROKER] as [Broker Person]
,[COVER] as [Cover]
,[GROSS] as [Gross]
,[NET_CLAIM] as [Net Claim]
,[OUTSTANDING] as [Oustanding]
,[INSURER_CLAIM_NO]
,[CLIENT] as [Client]
,[CLIENT_ENTITY_NAME]
,[BROKERAGE] as [Broker]
,[PRODUCT_CODE] as [Product Code]
,[POLICY_NUMBER] as [Policy Number]
,[INSURER] as [Insurer]
FROM [Cons_DW_New].[dbo].[DWQMV_CIMSDAILY];


QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);
Temp:

Load

min(Date) as minDate,

max(Date) as maxDate

Resident DWU_CIMSDAILY;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate AS [Claim Registered],

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;