Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
Aleja54
New Contributor

load a field from another table in an if

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

Labels (3)
5 Replies
Partner
Partner

Re: load a field from another table in an if

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
New Contributor

Re: load a field from another table in an if

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

eliko_il
New Contributor

Re: load a field from another table in an if

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
New Contributor

Re: load a field from another table in an if

this doesn´t work

Partner
Partner

Re: load a field from another table in an if

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