topic Re: Date Format : Interpretation issue in QlikView

Date Format : Interpretation issue

jujucts — Thu, 23 Jul 2015 09:55:55 GMT

Hi evrybody,

I am facing a strange issue, I should nromally not (according to myself)

I have dates in XL File (date format cells)

When I load it to QV i get numbers (OK) but I can't tranform it under date format.

I will appreciate your knowledge to solve this

here are the files

bedt regards

juju

Re: Date Format : Interpretation issue

Kushal_Chawda — Thu, 23 Jul 2015 09:59:10 GMT

Use Trim() function on resident load

CrossTable(Month, Value, 3)

LOAD Country,

Specialty,

Brand,

[40940],

[40969],

[41000],

[41030],

[41061],

[41091],

[41122],

[41153],

[41183],

[41214],

[41244]

FROM

data.xlsx

(ooxml, embedded labels, table is Sheet1);

T2:

NoConcatenate

LOAD *,

trim(Month) as MONTH

Resident T;

DROP Table T;

Now MONTH will have Number format

Re: Date Format : Interpretation issue

avinashelite — Thu, 23 Jul 2015 10:00:38 GMT

try like this:

=Date(Num#(Month),'DD/MM/YYYY')

if you want month name :

Monthname(Num#(Month)) as Month_Name

Re: Date Format : Interpretation issue

swuehl — Thu, 23 Jul 2015 10:01:44 GMT

That's an odd behaviour of the CROSSTABLE LOAD prefix, columns labels will always be transformed to text values.

You'll need a resident load to correct for this:

CrossTable(Month, Value, 3)

LOAD Country,

Specialty,

Brand,

[40940],

[40969],

[41000],

[41030],

[41061],

[41091],

[41122],

[41153],

[41183],

[41214],

[41244]

FROM

data.xlsx

(ooxml, embedded labels, table is Sheet1);

NOCONCATENATE

LOAD Country, Specialty, Brand, MonthName(num#(Month)) as Month, Value

RESIDENT T;

DROP TABLE T;

Re: Date Format : Interpretation issue

ashfaq_haseeb — Thu, 23 Jul 2015 10:03:21 GMT

Try like this

=date(Num#(Month))

or use below code

T:
CrossTable(Month, Value, 3)
LOAD Country, 
     Specialty, 
     Brand, 
     [40940], 
     [40969], 
     [41000], 
     [41030], 
     [41061], 
     [41091], 
     [41122], 
     [41153], 
     [41183], 
     [41214], 
     [41244]
FROM
data.xlsx
(ooxml, embedded labels, table is Sheet1);

//EXIT Script;
NoConcatenate

t1:
Load 
Date(num#(Month)) as Date,
Value,
Country,
Specialty,
Brand
Resident T;
DROP Table T;

Regards

ASHFAQ

Re: Date Format : Interpretation issue

sorrakis01 — Thu, 23 Jul 2015 10:10:25 GMT

Hi,

try this

Regards

Re: Date Format : Interpretation issue

jujucts — Thu, 23 Jul 2015 11:30:03 GMT

Hi Swuehl,

Thank you taht works.

Just explain why Trilm is needed because there is apparently no blank

Juju

Re: Date Format : Interpretation issue

jujucts — Thu, 23 Jul 2015 11:31:09 GMT

Hi Ashfaq,

thank you it works too.

What I tried was date(data#(Month, 'DD/MM/YY))

Why num# and Not date# ?

Juju

Re: Date Format : Interpretation issue

jujucts — Thu, 23 Jul 2015 11:32:32 GMT

Hi Avinash,

thank you it works too.

What I tried was date(data#(Month, 'DD/MM/YY))

Why num# and Not date# ?

Juju

Re: Date Format : Interpretation issue

swuehl — Thu, 23 Jul 2015 11:33:23 GMT

Hi Swuehl,

Thank you taht works.

Just explain why Trilm is needed because there is apparently no blank

Juju

I haven't used trim() and I don't think it's necessary.

Trim() function was suggested in the first answer, by Kush141087

Re: Date Format : Interpretation issue

ashfaq_haseeb — Thu, 23 Jul 2015 11:34:00 GMT

Hi,

Num# will convert string to number.

Regards

ASHFAQ

Re: Date Format : Interpretation issue

jujucts — Thu, 23 Jul 2015 11:48:09 GMT

Hi Kush

Thank you taht works.

Just explain why Trilm is needed because there is apparently no blank

Juju

Re: Date Format : Interpretation issue

jujucts — Thu, 23 Jul 2015 11:48:47 GMT

Yes sorry, I clicked the wrong name

Re: Date Format : Interpretation issue

jujucts — Thu, 23 Jul 2015 11:51:04 GMT

Yes I know and normally date#() will interpret as well date and transform it

As my cells are in XL date format it should be directly interpreted by date

Juju

Re: Date Format : Interpretation issue

Kushal_Chawda — Thu, 23 Jul 2015 11:56:21 GMT

juju1204‌

What I understood using trim is that when you have numbers as text, you can simply use trim function to convert to actual number , no need to use num#

Re: Date Format : Interpretation issue

avinashelite — Thu, 23 Jul 2015 12:25:11 GMT

your month data is in text format , that why we need to use num#() and not the date#

Date#

date#(expression [ , format-code ])

The date# function evaluates the expression as a date according to the string given as format-code. If the format code is omitted, the default date format set in the operating system is used.

Examples:

The examples below assume the two following operating system settings:

	Default setting 1	Default setting 2
Date format	YY-MM-DD	M/D/YY

date#( A ) where A=8/6/97 returns:

	Setting 1	Setting 2
String	8/6/97	8/6/97
Number	-	35648

date#( A, 'YYYY.MM.DD' ) where A=1997.08.06 returns:

num#(expression [ , format-code[ , decimal-sep [ , thousands-sep] ] ])

The num# function evaluates the expression numerically according to the string given as format-code. Decimal separator and thousands separator can be set as third and fourth parameters. If the parameters 2-4 are omitted, the default number format set by script variables or in the operating system is used.

Examples:

The examples below assume the two following operating system settings:

	Default setting 1	Default setting 2
Number format	# ##0,#	#,##0.#

num#( A, '#' ) where A=35,648.375 returns:

	Setting 1	Setting 2
String	35,648.375	35648.375
Number	-	35648.375

num#( A, '#.#', '.' , ',') where A=35,648.375 returns:

	Setting 1	Setting 2
String	35,648.375	35,648.375
Number	35648.375	35648.375

num#( A, '#.#',',','.' ) where A=35648.375 returns:

	Setting 1	Setting 2
String	35648.375	35648.375
Number	35648375	35648375

num#( A, 'abc#,#' ) where A=abc123,4 returns:

	Setting 1	Setting 2
String	abc123,4	abc123,4
Number	123.4	1234

Re: Date Format : Interpretation issue

jujucts — Thu, 23 Jul 2015 14:06:00 GMT

Yes Avinash,

I read that too but i just don't understand why date# does not work in my case

01/04/12 is a date format

The date# function evaluates the expression as a date according to the string given as format-code.

so date#(Month, 'DD/MM/YY') should return a date

and date(date#(Month, 'DD/MM/YY'), 'DD.MM.YYYY') should return 01.04.2012

so is my question : why this does not work ?

Juju

Re: Date Format : Interpretation issue

swuehl — Thu, 23 Jul 2015 14:21:16 GMT

At what point of time in your script execution are you trying to parse the date?

Could you post your script?

As I said above, the CROSSTABLE will transform all column labels to text, and the column label is a textual number like 41030 when read from excel. so trying to interpret it with a date format will not work.

Re: Date Format : Interpretation issue

jujucts — Thu, 23 Jul 2015 14:31:25 GMT

Swuehl

That's an odd behaviour of the CROSSTABLE LOAD prefix, columns labels will always be transformed to text values.

that's the point I missed.

Thank you

Juju