Date Format : Interpretation issue

[jujucts]

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

[Kushal_Chawda]

Use Trim() function on resident load

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);

T2:

NoConcatenate

LOAD *,

trim(Month) as MONTH

Resident T;

DROP Table T;

Now MONTH will have Number format

[avinashelite]

try like this:

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

if you want month name :

Monthname(Num#(Month)) as Month_Name

[swuehl]

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:

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);

NOCONCATENATE

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

RESIDENT T;

DROP TABLE T;

[ashfaq_haseeb]

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

[sorrakis01]

Hi,

try this

Regards

[jujucts]

Hi Swuehl,

Thank you taht works.

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

Juju

[jujucts]

Hi Ashfaq,

thank you it works too.

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

Why num# and Not date# ?

Juju

[jujucts]

Hi Avinash,

thank you it works too.

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

Why num# and Not date# ?

Juju

[swuehl]

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