Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good morning,
I have this script which generates a table called 'BBDD' after adding the field 'FECHA' (date in english) in it.
BBDD:
LOAD H,
CMP,
M,
MCDO,
TO,
TTOO,
ID,
CTO,
FINI,
FFIN,
ACT,
ES,
INT,
FIPE,
FFPE,
THA,
Replace( BASE,'.',',') as BASE,
IMP,
FREC
FROM
C:\DATA_CON_20120917-115749.csv
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);
Join Load distinct
FIPE,
FFPE,
Date(FIPE + iterno() -1) as FECHA
resident BBDD
While iterno() <= 1 + FFPE - FIPE;
The table should result order by FECHA ASCENDANT but, because the field 'FECHA' is created in a second load (out of 'FROM', I suppose), I do not know where to place the command 'ORDER BY'.
How can I get my table 'BBDD' sorted by 'FECHA' (date)?
Thanks
Just add bolded text in a proper place:
///$tab Main
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='h:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';
SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';
SET DayNames='lun;mar;mié;jue;vie;sáb;dom';
///$tab Explosion
BBDD:
LOAD H,
CMP,
M,
MCDO,
TO,
TTOO,
ID,
CTO,
FINI,
FFIN,
ACT,
ES,
INT,
FIPE,
FFPE,
THA,
Replace( BASE,'.',',') as BASE,
IMP,
FREC
FROM
C:\DATA_CON_20120917-115749.csv
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);
join Load distinct
FIPE,
FFPE,
Date(FIPE + iterno() -1) as FECHA
resident BBDD
While iterno() <= 1 + FFPE - FIPE;
BBDDNew:
NoConcatenate
LOAD
*
RESIDENT BBDD
order by FECHA asc;
drop table BBDD;
rename table BBDDNew to BBDD;
///$tab Calendario
let varmindate = num(Peek('FECHA',0,'BBDD'));
let varmaxdate = num(Peek('FECHA',-1,'BBDD'));
let vartoday = num(Today());
DateField:
load Date($(varmindate) + rowno() -1 ) as TempDate
AutoGenerate
$(varmaxdate)-$(varmindate)+1;
MasterCalendar:
LOAD
TempDate as FECHA,
Day(TempDate) as c_day,
WeekDay(TempDate) as c_weekday,
Week(TempDate) as c_week,
Month(TempDate) as c_month,
Year(TempDate) as c_year,
Date (monthstart (TempDate) , 'MMM_YYYY') as c_monthyear,
Week(TempDate)&'-'&Year(TempDate) AS c_weekyear,
inyeartodate(TempDate, $(vartoday), 0) * -1 AS c_curytdflag,
inyeartodate(TempDate, $(vartoday), -1) * -1 AS c_lastytdflag
RESIDENT DateField
ORDER BY TempDate ASC;
DROP TABLE DateField;
Hi,
Try this:
//Join
temp:
Load distinct
FIPE,
FFPE,
Date(FIPE + iterno() -1) as FECHA
resident BBDD
While iterno() <= 1 + FFPE - FIPE Resident BBDD;
join (BBDD)
load * resident temp order by FECHA asc;
drop table BBDD;
Regards,
David
Hi David, must be something wrong in the sintaxis from the word "Resident". The sintaxis debugger underlined the script from that point.
Not recognized words after the sentence
join
temp:
Load distinct
FIPE,
FFPE,
Date(FIPE + iterno() -1) as FECHA
resident BBDD
While iterno() <= 1 + FFPE - FIPE Resident BBDD
While iterno() <= 1 + FFPE - FIPE Resident BBDD;
It gives me three Scripts errors:
Tabla no encontrada (Table not found)
join (BBDD)
load * resident temp order by FECHA asc
Línea de error de Script: (error line of script)
DateField:
load Date( + rowno() -1 ) as TempDate
AutoGenerate
-+1
Tabla no encontrada (Table not found)
DROP TABLES statement
Post all your script.
///$tab Main
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='h:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';
SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';
SET DayNames='lun;mar;mié;jue;vie;sáb;dom';
///$tab Explosion
BBDD:
LOAD H,
CMP,
M,
MCDO,
TO,
TTOO,
ID,
CTO,
FINI,
FFIN,
ACT,
ES,
INT,
FIPE,
FFPE,
THA,
Replace( BASE,'.',',') as BASE,
IMP,
FREC
FROM
C:\DATA_CON_20120917-115749.csv
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);
join Load distinct
FIPE,
FFPE,
Date(FIPE + iterno() -1) as FECHA
resident BBDD
While iterno() <= 1 + FFPE - FIPE;
///$tab Calendario
let varmindate = num(Peek('FECHA',0,'BBDD'));
let varmaxdate = num(Peek('FECHA',-1,'BBDD'));
let vartoday = num(Today());
DateField:
load Date($(varmindate) + rowno() -1 ) as TempDate
AutoGenerate
$(varmaxdate)-$(varmindate)+1;
MasterCalendar:
LOAD
TempDate as FECHA,
Day(TempDate) as c_day,
WeekDay(TempDate) as c_weekday,
Week(TempDate) as c_week,
Month(TempDate) as c_month,
Year(TempDate) as c_year,
Date (monthstart (TempDate) , 'MMM_YYYY') as c_monthyear,
Week(TempDate)&'-'&Year(TempDate) AS c_weekyear,
inyeartodate(TempDate, $(vartoday), 0) * -1 AS c_curytdflag,
inyeartodate(TempDate, $(vartoday), -1) * -1 AS c_lastytdflag
RESIDENT DateField
ORDER BY TempDate ASC;
DROP TABLE DateField;
There is no code:
"join (BBDD)
load * resident temp order by FECHA asc"
Where is the error occurs ?
Just add bolded text in a proper place:
///$tab Main
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='h:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';
SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';
SET DayNames='lun;mar;mié;jue;vie;sáb;dom';
///$tab Explosion
BBDD:
LOAD H,
CMP,
M,
MCDO,
TO,
TTOO,
ID,
CTO,
FINI,
FFIN,
ACT,
ES,
INT,
FIPE,
FFPE,
THA,
Replace( BASE,'.',',') as BASE,
IMP,
FREC
FROM
C:\DATA_CON_20120917-115749.csv
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);
join Load distinct
FIPE,
FFPE,
Date(FIPE + iterno() -1) as FECHA
resident BBDD
While iterno() <= 1 + FFPE - FIPE;
BBDDNew:
NoConcatenate
LOAD
*
RESIDENT BBDD
order by FECHA asc;
drop table BBDD;
rename table BBDDNew to BBDD;
///$tab Calendario
let varmindate = num(Peek('FECHA',0,'BBDD'));
let varmaxdate = num(Peek('FECHA',-1,'BBDD'));
let vartoday = num(Today());
DateField:
load Date($(varmindate) + rowno() -1 ) as TempDate
AutoGenerate
$(varmaxdate)-$(varmindate)+1;
MasterCalendar:
LOAD
TempDate as FECHA,
Day(TempDate) as c_day,
WeekDay(TempDate) as c_weekday,
Week(TempDate) as c_week,
Month(TempDate) as c_month,
Year(TempDate) as c_year,
Date (monthstart (TempDate) , 'MMM_YYYY') as c_monthyear,
Week(TempDate)&'-'&Year(TempDate) AS c_weekyear,
inyeartodate(TempDate, $(vartoday), 0) * -1 AS c_curytdflag,
inyeartodate(TempDate, $(vartoday), -1) * -1 AS c_lastytdflag
RESIDENT DateField
ORDER BY TempDate ASC;
DROP TABLE DateField;
Now it works, thank you.