Manipulating imported fields

[#1]

Hello,

I am using the following script in order to create a date from a time stamp:

....

Load    

     Timestamp,

     IF(Len(Timestamp)=14,Date(Date#(Timestamp,'YYYYMMDDhhmmss'),'DD/MM/YYYY'),

     Date(Date#(Timestamp,'YYYYMMDDhhmmss'),'DD/MM/YYYY'))

     as Transaction_Date,

     Week(Transaction_Date) as Semaine,

     Year(Transaction_Date) as Année,

...

  This script works fine to get my Transaction_Date; but when I add the last 2 lines in order to get the week and year of this date, the script stops with the statement that "Transaction_Date field connot be found or does not exists".

Any idea for sorting out this issue???

Thanks in advance.

Best regards.

Idriss

[avinashelite · Accepted solution]

Try like this

LOAD *,

     Week(Transaction_Date) as Semaine,

     Year(Transaction_Date) as Année;

Load   

     Timestamp,

     IF(Len(Timestamp)=14,Date(Date#(Timestamp,'YYYYMMDDhhmmss'),'DD/MM/YYYY'),

     Date(Date#(Timestamp,'YYYYMMDDhhmmss'),'DD/MM/YYYY'))

     as Transaction_Date,

[#3]

Hi Avinash,

The script is now working. Thanks a lot.

I just need to check my data integrity as I have quite a number of lines with missing dates.

Anyway thank you very much that pushing me a step forward!!!

Best regards.

Idriss