Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table 'steps' with the following field headers
event | count | __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.
How do I get the correct 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
;
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?
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
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
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;
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