Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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?

1 Solution

Accepted Solutions
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

;

View solution in original post

15 Replies
arvind_patil
Partner - Specialist III
Partner - Specialist III

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
Creator
Creator

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
Creator III
Creator III

Hi

Please try

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


regards

Anonymous
Not applicable
Author

Hi Rasly,

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

Thanks

Anonymous
Not applicable
Author

Hi Arvind,

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

Regards

kusumanchir
Creator
Creator

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
Creator III
Creator III

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
Partner - Specialist III
Partner - Specialist III

Hi Max,

Use Below:

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


Thanks,

Arvind Patil

arvind_patil
Partner - Specialist III
Partner - Specialist III

Hi Max,

Use Below:

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


Thanks,

Arvind Patil