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

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

Tue, 18 Sep 2012 07:56:11 GMT

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

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

daveamz — Tue, 18 Sep 2012 08:09:19 GMT

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

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

Tue, 18 Sep 2012 08:29:31 GMT

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

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

whiteline — Tue, 18 Sep 2012 08:38:28 GMT

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

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

Tue, 18 Sep 2012 09:01:39 GMT

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

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

whiteline — Tue, 18 Sep 2012 09:05:27 GMT

Post all your script.

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

Tue, 18 Sep 2012 09:35:19 GMT

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

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

whiteline — Tue, 18 Sep 2012 10:03:52 GMT

There is no code:

"join (BBDD)

load * resident temp order by FECHA asc"

Where is the error occurs ?

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

whiteline — Tue, 18 Sep 2012 10:10:07 GMT

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,

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;

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

Tue, 18 Sep 2012 11:59:38 GMT

Now it works, thank you.