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;
DROPTABLEQ;
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
;
DROPTABLEQ;
But when i load the next parto of the crosstable it gives me the same error:
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')).
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).
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.
