Weekstart not working

[#1]

Hello all,

I'm hoping someone out there can help me solve what I expect is a simple problem that I can't figure out myself.  I need to:

1. Take a time value (e.g. 2013-09-05T13:30:44Z) and turn it into a date format.

2. Save the date in a new field.

3. Take that date and find the Thursday of the previous week.

4. Save the previous Thursday as a new field.

Step 4 is where my code below is not working.  I get blank "Created_On_Week" values.   Any help would be greatly appreciated!

-Josh

SET DateFormat='MM/DD/YYYY';

Table_one:

LOAD created_date_time,

MakeDate(

  Left(created_date_time, 4),

  Mid(created_date_time,6,2),

  Mid(created_date_time,9,2)) as [created_on_date],

WeekStart(created_on_date, 0, -4) as [Created_on_Week]    <--- This doesn't work

FROM [<URL removed for posting>] (XmlSimple, Table is [DATA/data]);

[Clever_Anjos · Accepted solution]

Table_one:
LOAD *,
WeekStart(created_on_date, 0, -4) as [Created_on_Week] ;
LOAD created_date_time,
MakeDate(
  Left(created_date_time, 4),
  Mid(created_date_time,6,2),
  Mid(created_date_time,9,2)) as [created_on_date];  <--- This doesn't work
FROM [<URL removed for posting>] (XmlSimple, Table is [DATA/data]);

You´re tryning to use a function that expects a date and passing a string as parameter.

Using a pre-Load we can use the value of created_on_date

Best regards

[#3]

Newbie mistake!  Many thanks...I knew it would be a quick fix.

-Josh

[marcus_sommer]

This would also work in a single load then such strings could be directly converted.

Table_one:

LOAD date(date#(keepchar('2013-09-05T13:30:44Z', '0123456789-:'), 'YYYY-MM-DDhh:mm:ss'), 'MM/DD/YYY hh:mm:ss') as created_date_time

date(date#(keepchar('2013-09-05T13:30:44Z', '0123456789-:'), 'YYYY-MM-DDhh:mm:ss'), 'MM/DD/YYY') as created_on_date,

weekstart(date(date#(keepchar('2013-09-05T13:30:44Z', '0123456789-:'), 'YYYY-MM-DDhh:mm:ss'), 'MM/DD/YYY'), 0, -4) as Created_on_Week

FROM [<URL removed for posting>] (XmlSimple, Table is [DATA/data]);

- Marcus