Loop through multiple conditions

Question

Hello,

I am trying to trying to loop through a table of rules to pull in the alignment of my data. The keys on the transactional data, which alignment needs to be mapped to, are Account code, Company code, Department code, Channel code, Product code and Region code. The keys on the rules data are Account number, Company, Department, Channel, Product and Region. The Rules have wild characters included. Is there a way to loop through these rules/conditions and pull in the last match?
Below is the original format with rules grouped together.

Below is the same list but I have split out via text to column , split to rows. I replaced All with * and % with *.

Thank you in advance. 
Daniel

Dandil28 · Answer

Unfortunately I do not believe we can simplify the logic. If you see the first line and last line of the screenshot, it would provide the incorrect alignment.

If there was a way to compare as it loops through, I think the formula would be something like:

IF
IF [Rules.AccountNumber] = "All" THEN -1 ELSE StartsWith([MainDataSet.AccountNumber], Length([Rules.AccountNumber])) = [Rules.AccountNumber] ENDIF
IF [Rules.Company] THEN -1 ELSE StartsWith([MainDataSet.Company], Length([Rules.Company])) = [Rules.Company] ENDIF AND
IF [Rules.Department] THEN -1 ELSE StartsWith([MainDataSet.Department], Length([Rules.Department])) = [Rules.Department] ENDIF AND
IF [Rules.Channel] THEN -1 ELSE StartsWith([MainDataSet.Channel], Length([Rules.Channel])) = [Rules.Channel] ENDIF AND
IF [Rules.Product] THEN -1 ELSE StartsWith([MainDataSet.Product], Length([Rules.Product])) = [Rules.Product] ENDIF AND
IF [Rules.Region] THEN -1 ELSE StartsWith([MainDataSet.Region], Length([Rules.Region])) = [Rules.Region] ENDIF AND
THEN [Rules.Segment] ELSE Null() ENDIF

MatthewO · Answer

@Dandil28 It sounds like this list would serve as a reference table to match another data set to in order to determine it's segment? It is possible that you could accomplish this using a macro but the logic could get complex. I'm curious if it would be possible to simplify the logic. For example, comparing only the first 2 characters of a department code. In that scenario, you could extract the first two characters from both data sets using a Formula tool, then join the necessary values.

Dandil28 · Answer

Hey MatthewO,

Sorry for the confusion. I used the text to column to split into rows. I included both screenshots because I wasn't sure if it was easier to loop through screenshot 1 or 2. I am trying to compare my base data against these rules tables to pull in the segment. For example, if account number starts with #HC and Company is B068 and Department starts with 84 and any channel and any product and any region then align to segment A11. But the later in the iteration it could get updated because a rule further down the list could override it. Like if account number starts with #HC and Company is B068 and Department starts with 84 and any channel and product starts with 11 and any region then align to segment A22. This has to be done by each row in the base dataset.

Does this make more sense?