Date Problem

[Anonymous]

Hello All,

I have a budget data in Excel in which the format is 'mmm-yy' ,when I load the budget data in qlikview by using cross table concept then it convert into number format, My problem is that I want to convert it in date format but  it is not converting in date format in qlikview.

Please see the  budget data and application in attachment.

Need assistance

[PrashantSangle · Accepted solution]

Hi,

use num#() and date()

try like date(num#(Yourfield),'YourdateFormat')

Regards

[alexandros17]

If you have a month and year what is the criteria to transform in date? Maybe the first day of the month?

In this case you could use the makedate function ....

[Anonymous]

is the following line causes the Problem?

Date#(MonthYear) as MonthYear1 ,

the date# function formats a given string to a date Format and requires the Format to convert

you Need to tell qlikview to Show the date (in number) as date format

use date(yourdate,'MMM-YY')

that's the Problem?

[PrashantSangle]

Hi,

When load it using Cross Table then it will read as text instead of num there for you have to use NUM#()

to again convert it into Number.

Regards

[sunny_talwar]

Try this:

Table:

CrossTable(Date, Data, 3)

LOAD ItemCode,

     Pulp,

     UOM,

     [42005],

     [42036],

     [42064],

     [42095],

     [42125],

     [42156],

     [42186],

     [42217],

     [42248],

     [42278],

     [42309],

     [42339],

     [42370],

     [42401],

     [42430]

FROM

Budget_Data.xlsx

(ooxml, embedded labels, header is 3 lines, table is Pulp);

NewTable:

NoConcatenate

LOAD ItemCode,

  Pulp,

  UOM,

  Date(Num#(Date, '##')) as Date,

  Data

Resident Table;

DROP Table Table;

[Anonymous]

Hi Max dreamer,

Thanks for assistance.

It is working .