load a field from another table in an if

[Aleja54]

good morning,
I have a query, I have a table and I want it to show values of a field, but if this field is empty it shows the values of a field of another table, but it does not work
What is it?

[tbl_antic]:

LOAD `ANTIC_ESTADO`,
`ANTIC_NUMFAC`,

if(`ANTIC_NUMOB_ORI`='', `DEFAC_NUMOOBL`, `ANTIC_NUMOB_ORI`) as `ANTIC_NUMOB_ORI`,
`ANTIC_NUMOOBL`,
`ANTIC_RUTPROV`,
`ANTIC_TIPOB_ORI`,
`ANTIC_TIPOFAC`,
`ANTIC_TIPOOBL`,
`ANTIC_ANO`,
`ANTIC_AREAS`,
`ANTIC_AUTORIZADO`,
`ANTIC_PAGADO`,
`ANTIC_RENDIDO`

FROM [lib://mySQL Database Conection2/provi1/tbl_antic.qvd] (qvd) ;

[lalphonso]

Hi @Aleja54 ,

You could try using the "alt" function - which takes the first non-null value. 

e.g.

Load

alt(ANTIC_ESTADO,ANTIC_NUMFAC) as ANTIC_NUMOB_ORI,

...

Regards,

LA

[Aleja54]

what happens is that my problem is not the null, but the field I want to show comes from another table  

and I get the error that does not find that field

[eliko_il]

Hi,
In this case you need first to join the field to the main table, and then using the "alt" function or any different  approach  that handle NULL.
Example:
Table 1:
load 

       key,

      ANTIC_NUMOB_ORI

    ....

FROM [lib://mySQL Database Conection2/provi1/tbl_antic.qvd] (qvd) ;

left join

load 

       key,

      DEFAC_NUMOOBL

    ....

FROM [lib://mySQL Database Conection2/provi1/table2.qvd] (qvd) ;

store table1....(qvd);

Table2:

load

if(`ANTIC_NUMOB_ORI`='', `DEFAC_NUMOOBL`, `ANTIC_NUMOB_ORI`) as `ANTIC_NUMOB_ORI`

....

from [...table1](qvd);

[Aleja54]

this doesn´t work

[lalphonso]

Hi @Aleja54 ,

Sorry I didn't see your comment on the field being derived from a different table. 

You could load a mapping table first that has the fields from the second table - and then use the alt function to evaluate if the field from the first table is empty:

so the evaulation becomes

load .... 

alt(firstfield, ApplyMap('MappingTableName', <criteriafield>, <default_value>)), ...

Regards,

LA