Change value of field based on combination of two separate dimensions

[bullish35]

In my load script, I would like to do accomplish this:

IF LOB = 4105 or 4106 or 4107 or 4110

AND [Line Description] = P211103 - PolChgs-Surr Charges

CHANGE [Line Description] to P211103 - PolChgs-Surr ChargesDIR

How do I script this functionality into the following load script?

This community's response always amazes me, so I am going to say, "Thank you!" in advance. Ellen

Plan:

LOAD

[Account and Name] as [Line Description],

[Combined Product] as Subseg,

LOB,

Date

num

FROM

[SOE Plan.xlsx]

(

ooxml, embedded labels, table is [Q1 2009]); ooxml, embedded labels, table is [Q1 2009]);([Amount * -1], '#,##0.##', '.' , ',') as [Plan Amount]([Last Day of Query Period]) as [As of Date],

[Miguel_Angel_Baeyens]

Hello Ellen,

Assuming all the above are fields existing in the table you are loading, you should add a line in your LOAD like

If(Match(LOB, 4105, 4106, 4107, 4110) AND [Line Description] = P211103 - [PolChgs-Surr Charges], P211103 - [PolChgs-Surr ChargesDIR], [Line Description]) AS [Line Description],

Hope this helps.

EDIT: bracketed some field names.

[bullish35]

'P211103 - PolChgs-Surr Charges' and 'P211103 - PolChgs-Surr ChargesDIR' are values, not fields. Should I remove the brackets you inserted? Thank you Miguel!

[Miguel_Angel_Baeyens]

Hello Ellen,

Take the following example

If(Match(LOB, 4105, 4106, 4107, 4110) AND [Line Description] = 1000 - 500, 1000 - 7000, [Line Description]) AS [Line Description],

In case they are fixed values (not variables or other fields) then yes, you can remove the brackets and it will look like above.

Hope that helps

[bullish35]

Hello Miguel. Thank you for looking at this. I am still not able to do what I was hoping. I am going to upload the .qvw I am working with. I am creating a .qvd by pulling in data from an Excel workbook. Once the .qvd is created, I want to display all fields and data. However, while I'm loading the data from the .qvd, I want to change the value of one of the fields only if conditions apply to that field and one other field. The field whose value I want to change is [Line Description]. But I only want to change it's value if the value of [Line Description]=abc AND the value of [LOB] is equal to either 1, 2, or 3. When both conditions are met, I want to make [Line Description]=xyz instead of abc. I do not want to change the value of [LOB].

Your time and input is greatly appreciated. If looking at the .qvw doesn't provide any helpful information, I will try to approach my goal of changing the value of that field some other way.

[Miguel_Angel_Baeyens · Accepted solution]

Hello Ellen,

My guess is as follows

Load If(Match(LOB, 1, 2, 3) AND [Line Description] = 'abc', 'xyz', [Line Description]) AS [Line Description], Subseg, LOB, [As of Date], [Plan Amount]From SOEPlan.qvd (qvd);

Hope this helps!

[bullish35]

That did it! Thank you so much. Ellen