topic if condition fields from 2 tables in QlikView

if condition fields from 2 tables

Thu, 05 Jun 2014 03:20:02 GMT

Hi, I have an if condition that contains fields from 2 different tables so I tried to concatenate the tables and when run, I received error message saying the field is not found. This is my current script:

INVENT:

LOAD *, IF(INVENTLOCATION='BTSTORE',REORDERPOINT) AS 'ROP(BT)';

SQL SELECT

.(OTHER FIELDS)

ORDERQTY

INVENTLOCATION

FROM <FILENAME>;

CONCATENATE(INVENT)

SQL SELECT

REORDERPOINT

FROM <FILENAME>;

Basically I want to create a column that reads from 2 tables.

Thanks!

Re: if condition fields from 2 tables

eduardo_sommer — Thu, 05 Jun 2014 03:40:29 GMT

Hi, Rachel

This is not the meaning of the concatenate. To reference a field in the load command, it must exist in a precedeing load (in your case, the SQL SELECT) or it must exist in the file you are loading.

In your case, you can make a join of the two source tables, before the load or you load both tables, join them in load/join commands end only then you make the load of the INVENT table referencing the fields you need.

Eduardo

Re: if condition fields from 2 tables

Anonymous — Thu, 05 Jun 2014 03:55:14 GMT

Try something like this:

Tab1:

SQL SELECT

.(OTHER FIELDS)

ORDERQTY

INVENTLOCATION

FROM <FILENAME>;

concatenate

Load

fieldnames;

SQL SELECT

REORDERPOINT

FROM <FILENAME>;

noconcatenate

Final:

Load *, IF(INVENTLOCATION='BTSTORE',REORDERPOINT) AS 'ROP(BT)';

resident Tab1;

Re: if condition fields from 2 tables

Thu, 05 Jun 2014 04:00:37 GMT

Hi Rachel,

You can do something like in your script

Table1:

SQL SELECT *

From TB1;

Join

SQL SELECT *

FROM TB2;

New_Table:

Load * , (IF(INVENTLOCATION='BTSTORE',RECORDPOINT ) AS 'POP(BT)'

RESIDENT Table1;

drop table Table1;

Hope this will help you.

-- Rajendra

Re: if condition fields from 2 tables

eduardo_sommer — Thu, 05 Jun 2014 04:05:01 GMT

Hi Nitin,

It seems to me that you'll never have the fields INVENTLOCATION and REORDERPOINT on the same row. If this is true, the field [ROP(BT)] will always have a null content.

For this reason, I proposed a join between the two source tables.

Eduardo

Re: if condition fields from 2 tables

eduardo_sommer — Thu, 05 Jun 2014 04:07:23 GMT

I think this will work, as soon as you have joining fields in bothe tables (TB1 and TB2).

Eduardo

Re: if condition fields from 2 tables

Thu, 05 Jun 2014 04:54:49 GMT

Yes to solve this type problems we must join the table but need to remember join should be used appropriate according to data.