topic Transformation logic for date format conversion in QlikView

Transformation logic for date format conversion

Wed, 25 Nov 2020 16:16:04 GMT

Is there a function to verify a date field which is present in dd/mm/yyyy hh:mm:ss which is getting transformed mm/dd/yy hh:mm:ss.

Re: Transformation logic for date format conversion

sunny_talwar — Tue, 14 Mar 2017 16:31:08 GMT

For a date which looks like this

03/03/2017 or 04/03/2017.... it would be very difficult to know, but you can convert this into a number to see which date format is it actually formatting into...

Re: Transformation logic for date format conversion

Wed, 15 Mar 2017 06:58:11 GMT

But when it is converted to a number, we can get the same number irrespective of any format. That would be pretty difficult to compare the Date format conversion.

Re: Transformation logic for date format conversion

sunny_talwar — Wed, 15 Mar 2017 16:08:17 GMT

04/03/2017 as MM/DD/YYYY will give you a different number then 04/03/2017 as DD/MM/YYYY

Re: Transformation logic for date format conversion

Thu, 16 Mar 2017 08:06:26 GMT

For the same date lets say 03-Apr-2017 if it is going to be dd/mm/yyyy -03/04/2017 and mm/dd/yyyy -04/03/2017 the value remains same. How can we verify this scenario the date is getting converted to this format.

Re: Transformation logic for date format conversion

sasiparupudi1 — Thu, 16 Mar 2017 08:39:11 GMT

Check the setting of the following variable in your qvw which dictates the default format for the model

SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';

hth

Sas

Re: Transformation logic for date format conversion

jonathandienst — Thu, 16 Mar 2017 08:53:34 GMT

This is why I prefer ISO style dates like:

YYYY-MM-DD

YYYY/MM/DD

They are unambiguous.

Re: Transformation logic for date format conversion

sunny_talwar — Thu, 16 Mar 2017 10:01:43 GMT

I think I am not completely sure what you have and what you are looking to get.... Would you be able to share a sample and elaborate on your issue a little more?

Re: Transformation logic for date format conversion

Thu, 16 Mar 2017 14:05:45 GMT

I actually have two date fields like below with a date field in data base . I have a transformation logic for which we need to converted to another format i.e From the format of date F1 to date F2.

F1 - 8/25/2012 12:00:01 AM

F2 - 25/8/2012 12:00:01 AM

I have written the below code and it works fine for me now. Thank you all.

t1:

LOAD * INLINE [

F1, F2

8/25/2012 12:00:01 AM, 25/8/2012 12:00:01 AM

];

store * from t1 into t1_inline.qvd(qvd);

drop Table t1;

t2:

LOAD F1,

F2,if(Timestamp(Timestamp#(F1,'MM/DD/YYYY hh:mm:ss TT'),'DD/M/YYYY hh:mm:ss TT')=F2,'Y','N'),Timestamp(Timestamp#(F1,'MM/DD/YYYY hh:mm:ss TT'),'DD/M/YYYY hh:mm:ss TT') as converted_data

FROM

t1_inline.qvd(qvd);

Re: Transformation logic for date format conversion

sunny_talwar — Thu, 16 Mar 2017 16:12:17 GMT

You can simplify this, using this

LOAD F1,

F2,

If(TimeStamp#(F1,'MM/DD/YYYY hh:mm:ss TT') = F2,'Y','N') as Flag,

TimeStamp(TimeStamp#(F1,'MM/DD/YYYY hh:mm:ss TT'),'DD/M/YYYY hh:mm:ss TT') as converted_data

FROM

t1_inline.qvd(qvd);