Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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],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!
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.
'P211103 - PolChgs-Surr Charges' and 'P211103 - PolChgs-Surr ChargesDIR' are values, not fields. Should I remove the brackets you inserted? Thank you Miguel!
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
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.
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!
That did it! Thank you so much. Ellen