Qlik Community

Ask a Question

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
3 Keynotes, 100+ Breakout Sessions, 1 New York Times bestseller, and you. QlikWorld Online, May 10-12: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Concatenation field error on ODBC connection

HI:

I'm confused because when I used an extraction of information coming from a SQL Server database using Excel for prototyping the following load script worked:

[DATOS]:

LOAD [UBICACION],

    [FECHA FACTURA],

mid([FECHA FACTURA],1,4) as [AÑO],

mid([FECHA FACTURA],6,2) as [MES],

[AÑO]&[MES] AS [AÑOMES],

    [C.P.] AS CP

FROM [lib://BI-SenseAutomotriz/Informacion.xlsx]

(ooxml, embedded labels, table is DATOS);

So [AÑO],[MES] and [AÑOMES] new fields where created with partial extraction from [FECHA FACTURA] field and concatenation worked fine.

But using ODBC connection to original database script don't work.  It says field [AÑO] don't exist!.

Here is the script:

LIB CONNECT TO 'DATA';

LOAD UBICACION,

      "FECHA FACTURA",

      mid("FECHA FACTURA",1,4) as [AÑO],

      mid("FECHA FACTURA",6,2) as [MES],

      [AÑO]&[MES] AS [AÑOMES],

    "C.P.";

SQL SELECT UBICACION,

    "FECHA FACTURA",

    "C.P."

FROM RENUNIAPP.dbo."Custom_Clientes_Ventas_Autos";

Field not found - <AÑO>

 

I've tryed to change fields name with no "Ñ" spanish letter.

Any clues?.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

You can create a field in a load statement and in that same load statement assume it already exists in the source.

Try this instead:

LIB CONNECT TO 'DATA';

LOAD *, [AÑO]&[MES] AS [AÑOMES];

LOAD UBICACION,

      "FECHA FACTURA",

      mid("FECHA FACTURA",1,4) as [AÑO],

      mid("FECHA FACTURA",6,2) as [MES],

    "C.P.";

SQL SELECT UBICACION,

    "FECHA FACTURA",

    "C.P."

FROM RENUNIAPP.dbo."Custom_Clientes_Ventas_Autos";


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
Gysbert_Wassenaar

You can create a field in a load statement and in that same load statement assume it already exists in the source.

Try this instead:

LIB CONNECT TO 'DATA';

LOAD *, [AÑO]&[MES] AS [AÑOMES];

LOAD UBICACION,

      "FECHA FACTURA",

      mid("FECHA FACTURA",1,4) as [AÑO],

      mid("FECHA FACTURA",6,2) as [MES],

    "C.P.";

SQL SELECT UBICACION,

    "FECHA FACTURA",

    "C.P."

FROM RENUNIAPP.dbo."Custom_Clientes_Ventas_Autos";


talk is cheap, supply exceeds demand

View solution in original post

Not applicable
Author

Thanks Gysbert:

One thing I'm noticing is that there is no a clear or logical explanation about how come this works some situations and others don't.  Do you know, or someone else know a documentation where all the best practices are found?.

Thanks!.

Gysbert_Wassenaar

You can never use a value from a field before the field (and thus the value) exists. There are no exceptions.


talk is cheap, supply exceeds demand