topic Re: Date Logic in QlikView

Date Logic

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

HI All,

My source data has STCLDT (store close date) is a 6 digit numeric filed in YYMMDD format, so if a store closed in December in the year 2000, the format would be 00125. Can't use makedate() function and compare dates. I am using a below logic to change into date format.

So if the store close date number is less than 1300, it defaults to 010101, which is a number than can be converted to a date easily.

if(STCLDT < 1300 and STCLDT <> 0, 010101, STCLDT) as Close_Date,

Then, the below logic looks to see if there is a close date. if the numeric filed is zero, the store is open

if(Close_Date = 0, 'N', 'Y') as Str_Closed,

So in source data, if some enters a store closing date for next month. I wanted to make above flag it in a way that the flag should still read as open store until 1 week or few days from the actual closing date.

Re: Date Logic

dplr-rn — Tue, 22 Jan 2019 21:18:08 GMT

how about
f(Close_Date = 0, 'N',
if(today()<Date#(Close_Date ,'YYMMDD'),'N','Y') ) as Str_Closed
basically if close date is greater than today mark flag as 'N'
hope i understoof correctly

Re: Date Logic

jduenyas — Wed, 23 Jan 2019 00:59:29 GMT

"... is a 6 digit numeric filed in YYMMDD format..."

Does that mean that Jan 5th 2012 will be represented in your STCLDT as 120105?

If yes, Why are you looking for it to be less than 1300? Only a non-closed store (date is null (or 0)) will be less than 1300.

Otherwise, try this if(Close_Date = 0 OR Close_Date > Today(),'N','Y')

Re: Date Logic

jduenyas — Wed, 23 Jan 2019 01:09:20 GMT

Correction :

if(Close_Date = 0 OR Close_Date > (Today()+7),'N','Y')

That will give you a cutoff of a week before the date becomes effective for the store to be noted as closed.

Re: Date Logic

has75042 — Thu, 24 Jan 2019 19:22:17 GMT

Thanks for your reply.

if the store closed in April of 2004, since date the is in numeric field (40406), this number is less than today's date numeric field 192401, with this logic it is showing as open store.

Re: Date Logic

dplr-rn — Thu, 24 Jan 2019 19:27:22 GMT

So you dont have a day there. If so You will check the length of the field and make the full date

Re: Date Logic

has75042 — Thu, 24 Jan 2019 20:13:10 GMT

Yes, I do have a day there.
2004-04-06.

Re: Date Logic

dplr-rn — Thu, 24 Jan 2019 20:15:35 GMT

Not sure what the problem is then. is the format different?
you will need to play around with soem if statements and achieve what you need