Creating field using if statement - field A = field B and field C = field D...

[#1]

Hello to all,

I'm trying to create a field that when meet certain conditions gives me a status of a pipeline project.

So, IF (

[Customer Code] = [Customer Code - Pipeline] and [Product Group Code] = [Product Group Code - Pipeline]

  and [Market Sector Description] = [Market Sector Description - Pipeline] and [Ship to Country Name] = [Ship to Country Name - Pipeline], 'Pipeline',.....

The idea is that when the information in the [Customer Code] = [Customer Code - Pipeline] is the same and all other conditions, the field is named 'pipeline'.

The Script follows bellow:

[Main Data]:

LOAD

  if ([Customer Code] = [Customer Code - Pipeline] and [Product Group Code] = [Product Group Code - Pipeline]

  and [Market Sector Description] = [Market Sector Description - Pipeline] and [Ship to Country Name] = [Ship to Country Name - Pipeline], 'Pipeline',

  if ([Customer] = [Customer - Pipeline] and [Product Group Code] = [Product Group Code - Pipeline]

  and [Market Sector Description] = [Market Sector Description - Pipeline] and [Ship to Country Name] = [Ship to Country Name - Pipeline], 'Pipeline',

  if ([Customer Code] = [Customer Code - Pipeline] and [Product Group Description] = [Product Group Description - Pipeline]

  and [Market Sector Description] = [Market Sector Description - Pipeline] and [Ship to Country Name] = [Ship to Country Name - Pipeline], 'Pipeline',

  if ([Customer] = [Customer - Pipeline] and [Product Group Description] = [Product Group Description - Pipeline]

  and [Market Sector Description] = [Market Sector Description - Pipeline] and [Ship to Country Name] = [Ship to Country Name - Pipeline], 'Pipeline',

  'Active Sales')))) as Status

RESIDENT [Main Data];

Can anyone help me on that?

Thanks a lot!!!!!!

[Gysbert_Wassenaar]

Your load statement is creating a table with only one field Status. You probably want to load the other fields from the source table too:

[Main Data]:

LOAD *,

  if ([Customer Code] = [Customer Code - Pipeline] and [Product Group Code] = [Product Group Code - Pipeline]

  and [Market Sector Description] = [Market Sector Description - Pipeline] and [Ship to Country Name] = [Ship to Country Name - Pipeline], 'Pipeline',

  if ([Customer] = [Customer - Pipeline] and [Product Group Code] = [Product Group Code - Pipeline]

  and [Market Sector Description] = [Market Sector Description - Pipeline] and [Ship to Country Name] = [Ship to Country Name - Pipeline], 'Pipeline',

  if ([Customer Code] = [Customer Code - Pipeline] and [Product Group Description] = [Product Group Description - Pipeline]

  and [Market Sector Description] = [Market Sector Description - Pipeline] and [Ship to Country Name] = [Ship to Country Name - Pipeline], 'Pipeline',

  if ([Customer] = [Customer - Pipeline] and [Product Group Description] = [Product Group Description - Pipeline]

  and [Market Sector Description] = [Market Sector Description - Pipeline] and [Ship to Country Name] = [Ship to Country Name - Pipeline], 'Pipeline',

  'Active Sales')))) as Status

FROM MySourceTable;

If I misunderstood your problem then please explain what the problem is.

[#3]

Hello gwassenaar, thanks for your reply.

I'm new at QlikView so I think my question was a little bit confusing.

I'll put some prints bellow so you can understand my problem.

This is my 'main data' tab, where most of the information is concentrated in my data model.

This is the tab where I'm trying to include information about pipeline status.

What I need is to include in my 'Main Data' a new field with the pipeline status, when a certain condition is met.

In the 'Main Data' file there isn't this field, so I'm not sure if the right way is to concatenate it

After I included the '*,' after the LOAD, this error occured:

Thanks a lot!!!!

[Gysbert_Wassenaar]

The error you get is because you're trying to add all fields using the * and then create another field with a name that's already part of the list of fields retrieved with the *, i.e. the field Status. You can't load it twice in the same record.

As for your concatenations, I don't understand what you're doing. The if statement will never match any record. The first load doesn't contain the pipeline fields so they can't be compared to other fields in those records. The second doesn't contain the original field names, but only the pipeline fields. So they can't be compared to fields in those records.

Perhaps you want a join instead of a concatenate, but I can't tell how the records from the first load are related to those of the second load.

[#5]

Hey Marco,

Are you brazilian? if so, contact me, I think you are going in the wrong way.

1- You are concatenating the same Table (Main Data). Is it all right?

2- When you call RESIDENT [Main Data], the "Customer Code" doens't exist anymore, you have renamed it to

"[Pipeline_Customer Code]".

3- You are trying to load status twice...

I'm not sure but the Dual Load could help. I need to undertand what you are trying to do.

Giba

[#6]

Hello Gysbert Wassenaar,

I need a way to include a need colunm in my [Main Data] when it finds in another table a combination of conditions.

When I used to do this in Excel, I normally used 3 concatenations in both databases and after that I used a VLOOKUP formula to include the information I need at my [Main Data].

So, I now understand that using a simple concatenation in QlikView won't work, but I'm still kind of lost.

Thanks!!

[Gysbert_Wassenaar]

Can you post a small qlikview document and some small example tables that explain what you're trying to do?