topic Re: Manipulating imported fields in QlikView

Manipulating imported fields

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Manipulating imported fields

avinashelite — Thu, 23 Feb 2017 15:34:10 GMT

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,

Re: Manipulating imported fields

Thu, 23 Feb 2017 16:13:43 GMT

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