topic Can´t extract year and month from date field in QlikView

Can´t extract year and month from date field

Mon, 04 Jan 2010 21:54:43 GMT

hi,

When i load a crosstable the date is loaded in a text format (not date format), so i load the table again using resident to take date in a date and number format. Finally i drop the original crosstable.

I need to extract the year and the month from the date field but when i reload the script it gives me an error becouse the date is taken as a text.

Format of crosstable:

SEÑAL	Unidad	01/01/2009	01/02/2009	01/03/2009
AMERICA SPORTS	P	0,06	0,06	0,06
AMERICA SPORTS	Q	112.553	112.553	112.553
CANAL (A)	P	0,140	0,140	0,140
CANAL (A)	Q	112.553	112.553	112.553

Where fields in blue are DATE for the cross table; and the columns SEÑAL and Unidad are the Qualifiers.

This is what i´m trying to do in the script:

Load

Year(DATE) AS YEAR,

Month(DATE) AS MONTH

;

Q1:

CrossTable(DATE, Q, 3)

LOAD * FROM

(biff, embedded labels, header is 1 lines, table is [Budget Arg 2009$])

WHERE(Unidad = 'Q');

CANTIDADPESOS:

LOAD DATE(NUM#(FECHA)) AS DATE,

Q AS BUDGET

RESIDENT Q1;

DROP TABLE Q;

This is the script error:

Field not found - <DATE>

Q1:

CrossTable(DATE, Q, 3)

LOAD * FROM

. . . "

What can i do to can extract year and month from this table?

I tried to do this but it doesn´t work:

Q1:

CrossTable(DATE, Q, 3)

LOAD * FROM

(biff, embedded labels, header is 1 lines, table is [Budget Arg 2009$])

WHERE(Unidad = 'Q');

CANTIDADPESOS:

LOAD DATE(NUM#(FECHA)) AS DATE,

Q AS BUDGET

RESIDENT Q1;

Load

Year(DATE) as YEAR,

Month(DATE) as MONTH

;

DROP TABLE Q;

But when i load the next parto of the crosstable it gives me the same error:

Field not found - <DATE>

P1:

CrossTable(DATE, P, 3)

LOAD * FROM

. . . "

THANKS,

Can´t extract year and month from date field

prieper — Mon, 04 Jan 2010 23:33:49 GMT

Hi,
would be a bit easier, if you can attach the datasource to check on the formatting there, what appears to be a date, might be a number (which would be the usual way) - then you can "force" QV to interprete this with a preceding DATE. If it is a text-field, you can interprete it with a formula like DATE#(MyDate, 'DD/MM/YYYY'). If you wish the latter to show as a date, then it should read DATE(DATE#(MyDate, 'DD/MM/YYYY')).

HTH
Peter

Can´t extract year and month from date field

Tue, 05 Jan 2010 00:31:02 GMT

Thanks Peter,

i think i don´t understand what you say or it doesn´t work; now i can see ok the complete date, but i can´t extract year and month. i need it because i´ve got more tables and views from where i take dates but these works ok. I need to extract year and months from this excel to match with the same field on the data base.

i tried extracting them using a expression but it doesn´t work as i need (doesn´t match with the others dates).

thaks again!!

pablo.

Can´t extract year and month from date field

prieper — Tue, 05 Jan 2010 00:31:02 GMT

Before you determine month or year you should have a date. Understood that you have a problem to read the DATE as a real date. Once you have it you need to reload the table and insert month or year. Thus the script should look like

Q1:
CrossTable(DATE, Q, 3)
LOAD * FROM

(biff, embedded labels, header is 1 lines, table is [Budget Arg 2009$])
WHERE(Unidad = 'Q');

Q2: LOAD *, DATE(DATE) AS Date, MONTH(DATE) AS Month, YEAR(DATE) AS Year RESIDENT Q1;
DROP TABLE Q1;

If this does not help, please post some sample from your Excel.

Peter

Can´t extract year and month from date field

Tue, 05 Jan 2010 14:53:49 GMT

thank you very much Peter.

here i send you a document with the data i´m working and a portion of the excel. i can´t convert DATE in a date format.

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/4011.doc1.doc:550:0]

thanks!

pablo

Can´t extract year and month from date field

Tue, 05 Jan 2010 15:11:03 GMT

I´m sorry Peter. It worked!!

i´ve made a mistake about your answer but now i saw it.

Thank you very much

pablo.