Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Where to place 'Order by' after using a Join Load

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

1 Solution

Accepted Solutions
whiteline
Master II
Master II

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;

View solution in original post

9 Replies
daveamz
Partner - Creator III
Partner - Creator III

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

Not applicable
Author

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

whiteline
Master II
Master II

While iterno() <= 1 + FFPE - FIPE Resident BBDD;

Not applicable
Author

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

whiteline
Master II
Master II

Post all your script.

Not applicable
Author

///$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;

whiteline
Master II
Master II

There is no code:

"join (BBDD)

load * resident temp order by FECHA asc"

Where is the error occurs ?

whiteline
Master II
Master II

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;

Not applicable
Author

Now it works, thank you.