topic Re: Please help me on preceding load in QlikView

Please help me on preceding load

ananyaghosh — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am facing a problem for the below code:

[Data]:

load FILE_ID,

Timestamp(Timestamp#(UNPACKAGED_DATE_TIME,'DD-MMM-YY hh:mm:ss.fff TT'),'MM/DD/YYYY h:mm:ss[.fff] TT') as UNPACKAGED_DATE_TIME;

select FILE_ID, UNPACKAGED_DATE_TIME

from edi_e2e_user.abc_e2e_tracking_record where FILE_ID = 'ci1534054081616.19521239@sbycsaxy05_te';

when I execute the above code, I got nothing for UNPACKAGED_DATE_TIME, but when I hard coding date as

Timestamp(Timestamp#('11-AUG-18 08:38:01.000000000 PM','DD-MMM-YY hh:mm:ss.fff TT'),'MM/DD/YYYY h:mm:ss[.fff] TT') as UNPACKAGED_DATE_TIME

the data is coming well. Please suggest what should I do now.

Re: Please help me on preceding load

marcus_sommer — Mon, 13 Aug 2018 06:14:58 GMT

I seems that the timestamp-format from UNPACKAGED_DATE_TIME is a bit different to your expectation and therefore the timestamp#() conversion fails. Just comment the preceeding part to load the data without any transformation and then put UNPACKAGED_DATE_TIME within a tablebox to look for the real values and if there is no differences visible maybe to export/copy them into excel and/or an editor like notepad ++ to identify each single char.

- Marcus

Re: Please help me on preceding load

tresB — Mon, 13 Aug 2018 06:16:15 GMT

That probably means your timestamp format is not matching. What is your data source? How does it look if you load in qlik directly (without any formatting)? If it is already in number format, you could directly try loading like:

Timestamp(UNPACKAGED_DATE_TIME, 'MM/DD/YYYY h:mm:ss.fff TT') as UNPACKAGED_DATE_TIME;

Re: Please help me on preceding load

ananyaghosh — Mon, 13 Aug 2018 06:20:17 GMT

Hi,

The value for that field is:

'11-AUG-18 08:38:01.000000000 PM'

but the point is when I use it in a KPI, it is showing correct value, but when I use it in a preceding load it shows nothing.

Re: Please help me on preceding load

marcus_sommer — Mon, 13 Aug 2018 06:38:21 GMT

Something between your value and your specified formatting does not match. One point could be that your specified qlik-variables for MonthNames (by default at the beginning of your script in tab Main) and/or these regional-setting of your machine are different to your values - because the available month-name abbreviation - for ...AUG... - must fit with your values to be matchable.

Further you need to know exactly which chars are in your values - each additionally space or maybe a tab instead of a space or something similar will break the conversion. Therefore try my suggestion from above.

- Marcus

Re: Please help me on preceding load

ananyaghosh — Mon, 13 Aug 2018 06:42:09 GMT

Hi,

My data source is Oracle and Datatype is Timestamp.

My value at oracle field is: '11-AUG-18 08:38:01.000000000 PM'

When I use your expression the value is changed to: 12-08-2018 06:08:01 AM.

It is noted that I am using the below expression:

Timestamp(UNPACKAGED_DATE_TIME, 'MM/DD/YYYY h:mm:ss.fff TT') as UNPACKAGED_DATE_TIME

Please advise what should I do now?

Re: Please help me on preceding load

ananyaghosh — Mon, 13 Aug 2018 07:28:49 GMT

HI,

Please help me.I have replied already.

Re: Please help me on preceding load

marcus_sommer — Mon, 13 Aug 2018 07:59:24 GMT

I think this means that your values are already numerical timestamps and no strings because the timestamp() formatting doesn't returned NULL. Your noticed differences between origin and formatted value is caused through different timezones. For this you could try to adjust it with converttolocaltime() or with a manually adjusting with something like:

Timestamp(UNPACKAGED_DATE_TIME - (1/24*9.5), 'MM/DD/YYYY h:mm:ss.fff TT')

- Marcus

Re: Please help me on preceding load

ananyaghosh — Mon, 13 Aug 2018 08:57:34 GMT

Hi,

I have used the below code , but is showing 12th August 2018,

convertToLocalTime(UNPACKAGED_DATE_TIME, 'Eastern Time (US & Canada)') as DATE_TRAN

but the below code is working fine:

Date(Timestamp(UNPACKAGED_DATE_TIME - (1/24*9.5)), 'MM/DD/YYYY h:mm:ss.fff TT') as UNPACKAGED_DATE_TIME

So please advise me if my client's time zone format is: Eastern Time (US & Canada), then what will be the better option?

Re: Please help me on preceding load

marcus_sommer — Mon, 13 Aug 2018 09:13:06 GMT

It seems that your timezone-string isn't correct and missed an A by US and also the spelling of the first char of Canada seems to be a K (at least within the german help it looked in this way):

convertToLocalTime(UNPACKAGED_DATE_TIME, 'Eastern Time (USA & Kanada)')

- Marcus

Re: Please help me on preceding load

ananyaghosh — Mon, 13 Aug 2018 12:55:39 GMT

Hi,

I am using the below code:

date(ConvertToLocalTime(UNPACKAGED_DATE_TIME, 'Eastern Time (USA & Kanada)'),'MM/DD/YYYY')

but getting null value and if I am using the below code:

ConvertToLocalTime(UNPACKAGED_DATE_TIME, 'Eastern Time (USA & Kanada)')

I am getting Eastern Time (USA & Kanada) text.

Please help me on this situation.

Re: Please help me on preceding load

marcus_sommer — Mon, 13 Aug 2018 13:28:19 GMT

I just tried a few localization-strings and it seems that some don't work especially those ones which contain not only letters like () or - and I would consider it as a bug (either by parsing the strings or within the documentation). But using the following worked for me:

ConvertToLocalTime(UNPACKAGED_DATE_TIME, 'GMT-09:30')

Beside this your date() formatting will just show the values as date but they remain a timestamp. If you want to get a date you need something like this:

date(floor(YourTimestamp)) as Date

respectively

time(frac(YourTimestamp)) as Time

to get the time. Quite often it's useful to split timestamps in this way to reduce the number of distinct values of a field.

- Marcus

Re: Please help me on preceding load

ananyaghosh — Mon, 13 Aug 2018 14:18:31 GMT

Hi,

Your solution is worked for me:

ConvertToLocalTime(UNPACKAGED_DATE_TIME, 'GMT-09:30')

but when I have used the below solution

date(floor(YourTimestamp)) as Date

and

time(frac(YourTimestamp)) as Time

it does not work and 11th Aug is cjamhed to 12th Aug.

Thanks,

Sandip

Re: Please help me on preceding load

marcus_sommer — Mon, 13 Aug 2018 14:26:33 GMT

date(floor(YourTimestamp)) was just a simplification and you need to use your convert-expression instead of the YourTimestamp so that it should be look like:

date(floor(ConvertToLocalTime(UNPACKAGED_DATE_TIME, 'GMT-09:30')))

- Marcus

Re: Please help me on preceding load

ananyaghosh — Mon, 13 Aug 2018 15:16:50 GMT

Hi,

I am using the below code as per your suggestion:

Date(Timestamp(UNPACKAGED_DATE_TIME - (1/24*9.5)), 'MM/DD/YYYY h:mm:ss.fff TT') as UNPACKAGED_DATE_TIME,

ConvertToLocalTime(UNPACKAGED_DATE_TIME, 'GMT-09:30') as Total_Date,

date(floor(ConvertToLocalTime(UNPACKAGED_DATE_TIME, 'GMT-09:30'))) as DATE_ONLY,

time(frac(ConvertToLocalTime(UNPACKAGED_DATE_TIME, 'GMT-09:30'))) as Time_ONLY

are working fine for me. I think this is enough for my requirement.

Thanks,

Sandip

Re: Please help me on preceding load

ananyaghosh — Sat, 25 Aug 2018 12:41:21 GMT

Hi,

One quick question from my end is when daylight savings will start the I have to change this?

'GMT-09:30' to another value?

Re: Please help me on preceding load

marcus_sommer — Sun, 26 Aug 2018 10:44:59 GMT

With the above used manual offset of (1/24*9.5) there is no automatic to daylight savings like in functions like converttolocaltime() but you could calculate in also manually with something like:

(1/24*

(9.5+

if(daynumberofyear(UNPACKAGED_DATE_TIME) >= 80 or

daynumberofyear(UNPACKAGED_DATE_TIME) <= 265, 1, 0)))

The general logic should work but please check if used day-area is really correct in your timezone. Further it might need to be extended if it goes over several years and there are maybe leap years.

- Marcus

Re: Please help me on preceding load

ananyaghosh — Sun, 26 Aug 2018 16:54:20 GMT

Hi,

Is there any general logic exists that will work for all the coming years and it will independent of day light savings or leap year etc? or the problem is occurring to me as because I am using materialized view in oracle?

I wondering that I am not facing problem for oracle but I am facing problem for view now.

Thanks,

Sandip

Re: Please help me on preceding load

marcus_sommer — Mon, 27 Aug 2018 05:22:41 GMT

From a theoretical point of view functions like converttolocaltime() do cover all these possibilities but if they in your case don't work like expected - you could try it again maybe with some training-data for example querying the distinct timestamps of the last n years and applying on them the various qlik functions with all possible parameter-combinations - and you used a manual offset approach you will need to calculate it yourself whereby it mustn't be done everything within a single-expression. Daylight savings and leap years might be calculated within a master-calendar and their result could be mapped within an expression like above.

- Marcus