topic Re: Date issue in QlikView

Date issue

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I am having trouble with data that I'm pulling

So generally my dates are broken down to Century and Date which includes YY/MM/DD (i.e: Cen =20 + Date = 180201)

However some single digit years i.e '050302' is missing the '0' as the first digit so it shows up as '50302'

When I try to concatenate the Century and Date for those ones I get something like this 2050/4/24 but needs to look like 2005/04/24

How do I add a ZERO so the date shows up properly?

VLUPD = Date

if ( left(VLUPD,2) >=10,'Y','N') as TEST_FLAG,

left(VLUPD,2) as TEST_YY,

if(VLUPD >=10,MakeDate(Left(Text(VLUPC&VLUPD),4), Mid(Text(VLUPD),3,2),Right(Text(VLUPD),2)),

MakeDate(Left(Text(VLUPC&'0'&VLUPD),4), Mid(Text(VLUPD),3,2),Right(Text(VLUPD),2))) as [Last UPDATE Date],

Please let me know of any solution thanks so much

-Erk

zebhashmi — Thu, 01 Feb 2018 20:19:37 GMT

I think you can try

if(Len(VLUPD)<6,0&VLUPD,VLUPD) AS VLUPD1

datagrrl — Thu, 01 Feb 2018 20:22:04 GMT

How are your dates in 2000 showing up? Do you have dates from then?

The expression might have to be tweaked a little to deal with those.

Anonymous — Thu, 01 Feb 2018 21:45:42 GMT

Thank you for your reply Jahanzeb

if(Len(VLUPD)<6,0&VLUPD,VLUPD) AS VLUPD1,

left(VLUPD,2) as TEST_YY,

if(VLUPD <6,MakeDate(Left(Text(VLUPC&VLUPD),4), Mid(Text(VLUPD),3,2),Right(Text(VLUPD),2)),

MakeDate(Left(Text(VLUPC&'0'&VLUPD),4), Mid(Text(VLUPD),3,2),Right(Text(VLUPD),2))) as [Last UPDATE Date],

So something like that?

Anonymous — Mon, 05 Feb 2018 16:42:54 GMT

Try Num(VLUPD, '000000') as [Last UPDATE Date]