topic Re: Formating Excel data inline with qlikview in QlikView

Formating Excel data inline with qlikview

tinkerz1 — Tue, 29 Sep 2015 10:20:16 GMT

Hi,

The variable I have set in the QV load script is not matching the excel dates, so I am having difficulty concatenating the table.

I set the QV variable like this, I understand this to be in date format?

SET DateFormat='DD/MM/YYYY';

SET Month_of_Transaction='01/11/2015';

Then I concatenate the variable onto another table that is from excel

$(Month_of_Transaction) as [Historical Dates]

The excel dates are left aligned to they look like text, so I use this calculated dimension to return a date

=Date(num#([Historical Dates]),'DD/MM/YYYY')

But that turns my variable date to 31/12/1899

How can I set my variable date so when it is formatted in a calculated dimension it appears os 01/01/2015?

MK_QSL — Tue, 29 Sep 2015 10:22:34 GMT

What is the format of Date in excel?

sunny_talwar — Tue, 29 Sep 2015 10:23:11 GMT

Try this:

Date#($(Month_of_Transaction), 'DD/MM/YYYY') as [Historical Dates]

and then try the following dimension -> [Historical Dates]

avinashelite — Tue, 29 Sep 2015 10:26:05 GMT

try like this:

Date(Date#([Historical Dates]),'DD/MM/YYYY')

tinkerz1 — Tue, 29 Sep 2015 10:29:28 GMT

It seems no get corripted as it passes through the formula in the QV calculated Dimension

Date(num#([Historical Dates]),'DD/MM/YYYY')

I have posted my QVW

tinkerz1 — Tue, 29 Sep 2015 10:36:03 GMT

Its seems the dates from excel are converted to numbers in QV.

So 01/10/2015 becomes 42278

avinashelite — Tue, 29 Sep 2015 10:40:59 GMT

Hi Neil,

check your data once it has some junk values in the dates and then try as below

try like this:

SET Month_of_Transaction=makemade(2015,11,01);

and use Date(Date#([Historical Dates]),'DD/MM/YYYY')

if it not working please share the sample data

NOTE: avoid the syntactic key

Mon, 21 Dec 2015 18:34:06 GMT

it works very well. thanks