Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
maxalander
New Contributor III

Issues with date format

I have a table 'steps' with the following field headers

eventcount__KEY_2017-05-03

I want to extract the date in the third header and add it to another table. When extracting the date I get vDateRow == 2017-05-03, but it doesn't stay that way when loaded into the table. I've tried several different ways but I can't seem to get it right. In the table below you can see several of the methods I tried.

LET vDateRow = subfield(FieldName(3, 'steps'),'_',4);

[InputTable]:     

LOAD

Date($(vDateRow)) AS week_date,

    Date#($(vDateRow)) AS week_date_1,

    Date#($(vDateRow), 'YYYY-MM-DD') AS week_date_2,

     $(vDateRow) AS week_date_3,

     [event] as [event_community],

    [count] AS [count_community]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_steps]);

And this is the date format I get in the table. None of them are correct.

Screen Shot 2017-08-03 at 08.28.16.png

How do I get the correct date format?

1 Solution

Accepted Solutions
kusumanchir
Contributor

Re: Issues with date format

Hi Max,

After modifying as per my previous post, try loading the 'Inputtable ' as below,

[InputTable]:    

LOAD

'$(vDateRow)' AS week_date

AutoGenerate 1

;

15 Replies
arvind_patil
Valued Contributor III

Re: Issues with date format

Hi Max,

With the help of PurgeChar you can skip extra fields from date.

Also first convert into date and then check.

Like Below:

Date(PurgeChar(Date Field,'Unwanted Char')) as NewDate;

Thanks,
Arvind Patil

kusumanchir
Contributor

Re: Issues with date format

Hi Max,

After creating the variable it holds a string. So you may need to interpret and convert to date format.

Please try this way,

LET vDateRow = Date(Date#(subfield(FieldName(3, 'steps'),'_',4),'YYYY-MM-DD'),'YYYY-MM-DD');

kenphamvn
Contributor III

Re: Issues with date format

Hi

Please try

date(date#(KeepChar('__KEY_2017-05-03','0123456789'),'YYYYMMDD')) as NewDate


regards

maxalander
New Contributor III

Re: Issues with date format

Hi Rasly,

I've tried that as well and it gives me '2009' just as many other methods that I tried.

Thanks

maxalander
New Contributor III

Re: Issues with date format

Hi Arvind,

I'm not quite sure that I follow you. What characters do you suggest I remove and why?

Regards

kusumanchir
Contributor

Re: Issues with date format

Hi Max,

I tried this way and I got as Date as 3rd may 2017,

LET vDateRow = Date(num(Date#(subfield(FieldName(3, 'steps'),'_',4),'YYYY-MM-DD')),'DD-MM-YYYY')

pathiqvd
Contributor III

Re: Issues with date format

Hi,

Try this,

datetest:

Load *,

year(__KEY_new_date) as __KEY_YEAR,

month(__KEY_new_date) as __KEY_MONTH;

load *,

date(date#(SubField(__KEY_Field,'_',4),'YYYY-MM-DD'),'YYYY-MM-DD') as __KEY_new_date

Inline [

__KEY_Field,Salesk

__KEY_2017-05-03,100

__KEY_2017-05-10,200

__KEY_2017-05-15,300

__KEY_2017-05-20,400

__KEY_2017-05-25,500

];

Regards,

arvind_patil
Valued Contributor III

Re: Issues with date format

Hi Max,

Use Below:

=Date(Date#(PurgeChar('__KEY_2017-05-03','__KEY_'),'YYYY-MM-DD'),'DD-MMM-YYYY')


Thanks,

Arvind Patil

arvind_patil
Valued Contributor III

Re: Issues with date format

Hi Max,

Use Below:

=Date(Date#(PurgeChar('__KEY_2017-05-03','__KEY_'),'YYYY-MM-DD'),'DD-MMM-YYYY')


Thanks,

Arvind Patil

Community Browser