Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
juju1204
Contributor

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

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Date Format : Interpretation issue

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.

18 Replies

Re: Date Format : Interpretation issue

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

Re: Date Format : Interpretation issue

try like this:

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

if you want month name :

Monthname(Num#(Month)) as Month_Name

MVP
MVP

Re: Date Format : Interpretation issue

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;

Re: Date Format : Interpretation issue

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
Valued Contributor

Re: Date Format : Interpretation issue

Hi,

try this

Regards

juju1204
Contributor

Re: Date Format : Interpretation issue

Hi Swuehl,

Thank you taht works.

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

Juju

juju1204
Contributor

Re: Date Format : Interpretation issue

Hi Ashfaq,

thank you it works too.

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

Why num# and Not date# ?

Juju

juju1204
Contributor

Re: Date Format : Interpretation issue

Hi Avinash,

thank you it works too.

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

Why num# and Not date# ?

Juju

MVP
MVP

Re: Date Format : Interpretation issue

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

Community Browser