topic Re: Data Source Issue in QlikView

Data Source Issue

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

I got a unique challenge right now

I was putting my data (excel file) from SQL, I got the desired date field results (Month, Quarter and so on)

But for this work, I need to put the data directly from excel sheet... (Same structure, same field with the excel sheet on the SQL connection) but the date fields are missing.

Please see attached files for clarity!!

I can you help me with clues how I will debug it

Re: Data Source Issue

jonas_rezende — Tue, 26 Jul 2016 14:44:52 GMT

Hi, Akpofure Enughwure.

See if this works in script Excel, color red.

Date(Floor(Timestamp#([CH.DOC.LAST CHANGED AT], 'DD.MM.YYYY hh:mm:ss')), 'MM/DD/YYYY') as Date

Hope this helps!

Re: Data Source Issue

akpofureenughwu — Tue, 26 Jul 2016 14:52:15 GMT

Hello Melo, Just add the # to the expression, No change..

Thank bro.

Re: Data Source Issue

jonas_rezende — Tue, 26 Jul 2016 14:59:06 GMT

There also in hh:mm:ss.

Re: Data Source Issue

akpofureenughwu — Tue, 26 Jul 2016 15:08:03 GMT

Without the timestamp function, the field produces data like 16.05.2014 23.59.59 (see attached file)

I need to manipulate this 16.05.2014 23.59.59 data to produce day, month, quarter and year field for my report.

Introducing this expression:

Date(Floor(Timestamp#([CH.DOC.LAST CHANGED AT], 'DD.MM.YYYY hh:mm:ss')), 'MM/DD/YYYY') as Date

I don't get any result for day, monthm quarter ,year and even date.. I have been on this all day....

Re: Data Source Issue

maheshkuttappa — Tue, 26 Jul 2016 15:32:09 GMT

Try below

DATE(FLOOR(NUM(DATE#( [CH.DOC.LAST CHANGED AT], 'DD.MM.YYYY hh:mm:ss' ) )),'MM-DD-YYYY')

Re: Data Source Issue

akpofureenughwu — Tue, 26 Jul 2016 15:37:15 GMT

It worked.....

Thanks Jonas...

Why did u use this expression? Can u explain or better still send me a pdf to read up?

I'm grateful

With Regards

Akpofure