Create field based on values in 2 other fields

[hucand]

My model looks like this:

Row 1:

Product 1 Name - A

Product 1 Amount - 1

Product 2 Name - B

Product 2 Amount - 1

Product 3 Name - C

Product 3 Amount - 2

Product 4 Name - D

Product 4 Amount - 3

Product 5 Name - E

Product 5 Amount - 1

Combo Sale - True

Row 2:

Product 1 Name - D

Product 1 Amount - 1

Product 2 Name - E

Product 2 Amount - 1

Product 3 Name - B

Product 3 Amount - 1

Product 4 Name - A

Product 4 Amount - 0

Product 5 Name - C

Product 5 Amount - 1

Combo Sale - False

The product Names can be 1 of 5 different names: say A, B, C, D, and E

I want to add a column for "Combo Sale" that is True only if 2 different products are found with names A and B and a value > 1, like above

Is that possible?

[Vilo]

I think I follow what youre saying, but just to clarify, if both A + B have >1 then 'Combo Sale' = true? 

[hucand]

Yes, that's correct.

[Vilo · Accepted solution]

I was able to accomplish what youre looking for with if statements. With the below script I am first creating 2 flags to check if there is both A and B with an amount greater than 1, and the final flag checks if the first 2 flags are true, which then gives you your combo sale result. Let me know if this works for you. 

field_based_value:

NoConcatenate

LOAD * Inline [

Product 1 Name, Product 1 Amount, Product 2 Name, Product 2 Amount, Product 3 Name, Product 3 Amount, Product 4 Name, Product 4 Amount, Product 5 Name, Product 5 Amount

A, 1, B, 1, C, 2, D, 3, E, 1

D, 1, E, 1, B, 1, A, 0, C, 1

];

Field_based_logic:

Load

*,

    If(A_Flag = 1 and B_Flag = 1, 'True', 'False') AS Combo_Sale;

Load

*,

    If([Product 1 Name]='A' and [Product 1 Amount] >= 1, 1,

    If([Product 2 Name]='A' and [Product 2 Amount] >= 1, 1,

        If([Product 3 Name]='A' and [Product 3 Amount] >= 1, 1,

            If([Product 4 Name]='A' and [Product 4 Amount] >= 1, 1,

                If([Product 5 Name]='A' and [Product 5 Amount] >= 1, 1,  0))))) AS A_Flag,

    If([Product 1 Name]='B' and [Product 1 Amount] >= 1, 1,

    If([Product 2 Name]='B' and [Product 2 Amount] >= 1, 1,

        If([Product 3 Name]='B' and [Product 3 Amount] >= 1, 1,

            If([Product 4 Name]='B' and [Product 4 Amount] >= 1, 1,

                If([Product 5 Name]='B' and [Product 5 Amount] >= 1, 1, 0))))) AS B_Flag

Resident field_based_value;

Drop table field_based_value;