Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date question

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

5 Replies
trdandamudi
Master II
Master II

Can you post some sample data...

sunny_talwar

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,

Not applicable
Author

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!

sunny_talwar

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

Not applicable
Author

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