topic Re: Date question in QlikView

Date question

Wed, 25 Nov 2020 16:16:04 GMT

Date not getting the format expected

// Load script

reporting_date:
LOAD @1 as date
FROM
[.\reporting_date.txt]
(txt, codepage is 1252, no labels, delimiter is '\t', msq);

LET COB_DATE = peek('date',0,'reporting_date');

// dates
LET COB_DATE_string = date(date#('$(COB_DATE)','DD/MM/YYYY'),'YYYYMMDD');

data:
LOAD
ROWNO() AS ROW_NO,
$(COB_DATE_string) as BUSINESS_DATE,

Now when I load the data I am getting the date as 03/06/57098

However, if I load the data with the code

data:
LOAD
ROWNO() AS ROW_NO,
$(COB_DATE) as BUSINESS_DATE,

Now when I load the data I am getting the date as 30/12/1899

However, in the reporting file the date is 11/08/2016

How do I get the value of 11th August 2016 instead of the incorrect date format

Re: Date question

trdandamudi — Mon, 15 Aug 2016 11:50:19 GMT

Can you post some sample data...

Re: Date question

sunny_talwar — Mon, 15 Aug 2016 12:05:03 GMT

How about trying like this:

reporting_date:
LOAD Num(@1) as date
FROM
[.\reporting_date.txt]
(txt, codepage is 1252, no labels, delimiter is '\t', msq);

LET COB_DATE = peek('date',0,'reporting_date');

data:
LOAD
ROWNO() AS ROW_NO,
Date($(COB_DATE), 'YYYYMMDD') as BUSINESS_DATE,

Re: Date question

Mon, 15 Aug 2016 12:14:12 GMT

I tried this and this worked fine.

LET COB_DATE_string = date(date#('$(COB_DATE)','DD/MM/YYYY'),'YYYYMMDD');
LET COB_DATE_string2 = date(date#('$(COB_DATE)','DD/MM/YYYY'),'DD/MM/YYYY');

data:
LOAD
ROWNO() AS ROW_NO,
'$(COB_DATE_string2)' as BUSINESS_DATE,

I suppose does the trick!

Re: Date question

sunny_talwar — Mon, 15 Aug 2016 13:29:58 GMT

I guess I am glad that you found your solution, but I feel you are making it way more complicated then it needs to be. But again, if this gives you want and my provided way isn't working, then I won't ask you to waste your time with my response

Please close the thread by marking appropriate response as correct and helpful.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: Date question

Mon, 15 Aug 2016 13:35:16 GMT

I did try your way which did not work. However, I would prefer a more effective solution.#

reporting_date:
LOAD Num(@1) as date
FROM
[.\reporting_date.txt]
(txt, codepage is 1252, no labels, delimiter is '\t', msq);

LET COB_DATE = peek('date',0,'reporting_date');

This gives COB date to be a number instead of a date which I can use