Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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
Honored Contributor II

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

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;

9 Replies
daveamz01
Contributor III

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

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

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

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
Honored Contributor II

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

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

Not applicable

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

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
Honored Contributor II

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

Post all your script.

Not applicable

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

///$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
Honored Contributor II

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

There is no code:

"join (BBDD)

load * resident temp order by FECHA asc"

Where is the error occurs ?

whiteline
Honored Contributor II

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

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

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

Now it works, thank you.

Community Browser