topic Re: load a field from another table in an if in App Development

load a field from another table in an if

Aleja54 — Sat, 16 Nov 2024 05:53:14 GMT

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

Re: load a field from another table in an if

lalphonso — Sat, 11 May 2019 03:37:25 GMT

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,

Re: load a field from another table in an if

Aleja54 — Sun, 12 May 2019 04:10:35 GMT

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

Re: load a field from another table in an if

eliko_il — Sun, 12 May 2019 13:54:48 GMT

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

Re: load a field from another table in an if

Aleja54 — Sun, 12 May 2019 23:57:51 GMT

this doesn´t work

Re: load a field from another table in an if

lalphonso — Mon, 13 May 2019 00:05:01 GMT

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,