Qlik Community

App Development

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

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
Aleja54
Contributor
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
lalphonso
Partner
Partner

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

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

eliko_il
Contributor
Contributor

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

this doesn´t work

lalphonso
Partner
Partner

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