topic Re: Date Problem in QlikView

Date Problem

Anonymous — Wed, 09 Sep 2015 09:01:41 GMT

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

Re: Date Problem

PrashantSangle — Wed, 09 Sep 2015 09:05:57 GMT

Hi,

use num#() and date()

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

Regards

Re: Date Problem

alexandros17 — Wed, 09 Sep 2015 09:06:26 GMT

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

Re: Date Problem

Anonymous — Wed, 09 Sep 2015 09:07:08 GMT

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?

Re: Date Problem

PrashantSangle — Wed, 09 Sep 2015 09:07:22 GMT

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

Re: Date Problem

sunny_talwar — Wed, 09 Sep 2015 09:10:43 GMT

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;

Re: Date Problem

Anonymous — Wed, 09 Sep 2015 09:12:32 GMT

Hi Max dreamer,

Thanks for assistance.

It is working .