Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jujucts
Partner - Creator II
Partner - Creator II

Date Format : Interpretation issue

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

18 Replies
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
MVP
MVP

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
Champion III
Champion III

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
Specialist
Specialist

Hi,

try this

Regards

jujucts
Partner - Creator II
Partner - Creator II
Author

Hi Swuehl,

Thank you taht works.

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

Juju

jujucts
Partner - Creator II
Partner - Creator II
Author

Hi Ashfaq,

thank you it works too.

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

Why num# and Not date# ?

Juju

jujucts
Partner - Creator II
Partner - Creator II
Author

Hi Avinash,

thank you it works too.

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

Why num# and Not date# ?

Juju

swuehl
MVP
MVP

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