Help with IF Statement

[stuwannop]

Hi All

What I want the following IF statement to do is, IF the APPSilo field is blank, return the PInstallTeam, otherwise just use the APPSilo field.

IF(IsNull(APPSilo),PInstallTeam,APPSilo)

Nothing wrong with the logic there of course. The problem I am having is the PInstall team is in another table (created previously in the script). The tables are linked via a key field. How do I get the script to return the PInstallTeam field - the error is Field not Found. Thanks.

[jagannalla]

You are running the script which doesn't have PInstallTeam feild from particular dataset.

For eg:

I've data in my dataset

A     B     C

If i want to load this dataset in qvw. I can use the code as,

Load A,B,C

From dataset;

But if i use the below code, it show the error. B' coz D,E fields are not existing in the datset.

Load A,B,C,

          D,E

From dataset;

[stuwannop]

Thanks Jagan - I have the logic and I know why it is showing the error. My question is how do I load the PInstallTeam into the existing data table from another one so I can run my IF statement.

[jagannalla]

First you should have a common key between two tables. With help of common key we can combine two tables. There are different number of ways to combine the two tables, it all depends on the data what we want. From the combination of two tables we can make a calcualation.

For eg:

Table1:

Load key, A,B,C from Dataset1;

Left Join (Table1)

Load key,D,E from Dataset2;

Load key,

     If(IsNull(C),A,E) as NewField

Resident Table1;

Hope it may helps you understand. Otherwise provide sample file with data. It helps me and others to solve the issue of yours.

[stuwannop]

I'm pretty sure it's the lookup command but can't figure it beyond there.

[Miguel_Angel_Baeyens]

Hi,

Check the following example and adapt it to meet your needs:

Table1:
LOAD CustomerID,
     CustomerName
     Address,
     CustomerCode,
     ZipCode,
     City,
     Country,
FROM Customers.qvd (qvd);
// take all customer IDs and return codes
IDCodeMap:
MAPPING LOAD CustomerID,
     CustomerCode
RESIDENT Table1;
// Replace empty CustomerID with Customer Code
Table2:
LOAD InvoiceID,
     Date,
     CustomerID,
     If(Len(CustomerCode) = 0, ApplyMap('IDCodeMap', CustomerID, CustomerCode), CustomerCode) AS CustomerCode
FROM Invoices.qvd (qvd);

Hope that helps.

Miguel