topic Re: Week number in QlikView

Week number

Thu, 19 Mar 2015 14:12:57 GMT

I am trying to have the week number, but i can not.

The script below work well , but when i add ..year(orddatum) as Year,month(orddatum) as Month,week(orddatum) as Week,...

or DATEPART(WEEK,orddatum) the scritp now work anymore.

In the db the date are like "09/01/2013 00:00:00"

But it seems to recognize also 'YYYY-MM-DD hh:mm:ss[.fff]' format
Thanks

SET

ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;-€ #.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='YYYY-MM-DD';
SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]';
SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';
SET DayNames='lun;mar;mer;gio;ven;sab;dom';
ODBC CONNECT TO AirArtecSu;
Alonte:
//-------- Start Multiple Select Statements ------
SQL SELECT "alfa_alsis_scope" as AlsisScope,artbeskr as Descrizione,artikelvariant as ItemID,extordtyp,ordantal as Quantità,orddatum as Date,year(orddatum) as Year,month(orddatum) as Month,day(orddatum) as Day,saljare as Alsis,varugruppkod,"vb_pris" as TransfertPrice,ordernr as OrderNumber,ftgnr as AdministrativeEntity,ordrestnr
FROM ArtecAirSU.dbo.orp where orddatum>{ts '2013-01-01 00:00:00'} and varugruppkod<>'B';
Inner Join
SQL SELECT kundbestnr as CustomerOrderNumber,kundref2 as OrderHandler,ordstat as OrderStatus,slutkundnamn1 as Customer,ordernr as OrderNumber,ftgnr as AdministrativeEntity,ordrestnr
FROM ArtecAirSU.dbo.oh;
Inner Join
SQL SELECT ftgnamn,ftgnr as AdministrativeEntity
FROM ArtecAirSU.dbo.fr;

//-------- End Multiple Select Statements ------

Re: Week number

Fri, 20 Mar 2015 08:40:08 GMT

Hi again

The error was

SQL##f - SqlState: 37000, ErrorCode: 195, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]'week' is not a recognized built-in function name.
Alonte:

Re: Week number

marcus_sommer — Fri, 20 Mar 2015 10:24:40 GMT

Week won't be a supported function from your odbc-driver and/or the database. Use for this a preceeding load:

Load *, week(orddatum) as Week;

SQL Select ... From ...;

- Marcus

Re: Week number

Fri, 20 Mar 2015 10:46:27 GMT

Hi Markus,

thanks for your kind reply. as you can see below I have insert the statment, but it give still the error.

ODBC

CONNECT TO AirArtecSu ;
Alonte:
//-------- Start Multiple Select Statements ------
SQL SELECT "alfa_alsis_scope" as AlsisScope,artbeskr as Descrizione,artikelvariant as ItemID,extordtyp,ordantal as Quantità,orddatum as Date,year(orddatum) as Year,week(orddatum) as Week,month(orddatum) as Month,day(orddatum) as Day,saljare as Alsis,varugruppkod,"vb_pris" as TransfertPrice,ordernr as OrderNumber,ftgnr as AdministrativeEntity,ordrestnr
FROM ArtecAirSU.dbo.orp where orddatum>{ts '2013-01-01 00:00:00'} and varugruppkod<>'B' ;

SQL##f - SqlState: 37000, ErrorCode: 195, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]'week' is not a recognized built-in function name

Re: Week number

marcus_sommer — Fri, 20 Mar 2015 11:03:14 GMT

You need to remove these week-statement from your SQL Select part. Maybe you removed all transformations from this and make them inside the preceeding load statement.

- Marcus

Re: Week number

Fri, 20 Mar 2015 11:24:05 GMT

Sorry I cannot understand, therefore DB is not select yet..

I tryed to move also after sql select.. but it not work

ODBC CONNECT TO AirArtecSu;
Alonte:
Load *, week(orddatum) as Week;
SQL SELECT "alfa_alsis_scope" as AlsisScope,artbeskr as Descrizione,artikelvariant as ItemID,extordtyp,ordantal as Quantità,orddatum as Date,year(orddatum) as Year,month(orddatum) as Month,day(orddatum) as Day,saljare as Alsis,varugruppkod,"vb_pris" as TransfertPrice,ordernr as OrderNumber,ftgnr as AdministrativeEntity,ordrestnr
FROM ArtecAirSU.dbo.orp where orddatum>{ts '2013-01-01 00:00:00'} and varugruppkod<>'B' ;

in fact I have this error

Field not found - <orddatum>
SQL SELECT "alfa_alsis_scope" as AlsisScope,artbeskr as Descrizione,artikelvariant as ItemID,extordtyp,ordantal as Quantità,orddatum as Date,year(orddatum) as Year,month(orddatum) as Month,day(orddatum) as Day,saljare as Alsis,varugruppkod,"vb_pris" as TransfertPrice,ordernr as OrderNumber,ftgnr as AdministrativeEntity,ordrestnr
FROM ArtecAirSU.dbo.orp where orddatum>{ts '2013-01-01 00:00:00'} and varugruppkod<>'B' ;

Re: Week number

marcus_sommer — Fri, 20 Mar 2015 11:27:19 GMT

You renamed orddatum to Date within the sql - therefore you need:

....

Load *, week(Date) as Week;

....

- Marcus

Re: Week number

Fri, 20 Mar 2015 11:34:31 GMT

Too kind!

it is working now!

Really many thanks