Connectivity & Data Prep

[#1]

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?.

[Gysbert_Wassenaar · Accepted solution]

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";

[#3]

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.