Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

if condition fields from 2 tables

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!

6 Replies
eduardo_sommer
Valued Contributor

Re: if condition fields from 2 tables

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

gupta_n8
Valued Contributor II

Re: if condition fields from 2 tables

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;

Not applicable

Re: if condition fields from 2 tables

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

eduardo_sommer
Valued Contributor

Re: if condition fields from 2 tables

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

eduardo_sommer
Valued Contributor

Re: if condition fields from 2 tables

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

Eduardo

Not applicable

Re: if condition fields from 2 tables

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

Community Browser