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
;
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
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');
Hi
Please try
date(date#(KeepChar('__KEY_2017-05-03','0123456789'),'YYYYMMDD')) as NewDate
regards
Hi Rasly,
I've tried that as well and it gives me '2009' just as many other methods that I tried.
Thanks
Hi Arvind,
I'm not quite sure that I follow you. What characters do you suggest I remove and why?
Regards
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')
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,
Hi Max,
Use Below:
=Date(Date#(PurgeChar('__KEY_2017-05-03','__KEY_'),'YYYY-MM-DD'),'DD-MMM-YYYY')
Thanks,
Arvind Patil
Hi Max,
Use Below:
=Date(Date#(PurgeChar('__KEY_2017-05-03','__KEY_'),'YYYY-MM-DD'),'DD-MMM-YYYY')
Thanks,
Arvind Patil