Date question

[#1]

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

[trdandamudi]

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,

[#4]

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

[#6]

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