Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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?

15 Replies
kusumanchir
Creator
Creator

Hi Max,

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

[InputTable]:    

LOAD

'$(vDateRow)' AS week_date

AutoGenerate 1

;

Anonymous
Not applicable
Author

There we have it!

The issue was not with getting vDateRow in the right format but instead when entering it into the table.

Will my week_date be a string instead of a date now?

kusumanchir
Creator
Creator

Hi Max,

As we have the variable holding a string which is date format, so while loading we need to quote it.

Regards,

Rasly.K

Anonymous
Not applicable
Author

Hi Arvind,

The issue is not to get the date in the right format in the variable but when inserting it to the table. Look at the latest answer from Rasly.

Thank you for your help

Anonymous
Not applicable
Author

I'm just adding some notes here to summarise the comments with my solution.

No more formatting with the date was needed but instead only what making sure to insert it into the table in the right way. Using the following code solves the problem:

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


[InputTable]:    

LOAD

     '$(vDateRow)' AS week_date,

AUTOGENERATE 1;

ahaahaaha
Partner - Master
Partner - Master

Hi,

May be like this

If(Left(Trim($Field), 6)='__KEY_',

Date(Date#(Right($Field, Len($Field)-6), 'YYYY-MM-DD'))

)


Example at attached file

Regards,

Andrey