Hello!
I have a database with orders (transactional information with invoices, part numbers, customer names/addresses, etc). Some of these are duplicated with the exception of a "segment" and "status" field. This happens due to users uploading the same information to multiple segment partitions within the same database. I need to create a field that specifies if that invoice number exists as a duplicate in the same table with the value of the Segment and Status fields being different. More details:
Field "Segment" possible values:
- Large Business
- Medium Business
- Small Business
Field "Status" possible values:
- Approved
- Rejected
Example:
User uploads data to Large Business segment including all transactions for large, medium, and small. In the Large Biz segment partition, transactions with customers that are approved large businesses are marked with Status = "Approved". The other small and medium biz customers uploaded to the Large Biz segment are marked with "Rejected".
The same applies to the other Segments, where ALL data are uploaded to each of the three, but only the appropriate customers tied to that segment where it was uploaded (i.e. medium segment uploaded transaction only marks "approved" on medium segment customers) are marked as approved, and all others are marked as Rejected (Large biz customers uploaded to Medium Biz are marked "Rejected", etc)
Need:
I want to have a field that dictates if a transaction (invoice number) exists as a duplicate or if it is approved in a certain segment. These would be the output values of the calculation
- "Approved in Large Business"
- "Approved in Medium Business"
- "Approved in Small Business"
- "Duplicate or Not Valid"
The way I would use it would be as a filter where I exclude "Duplicate or Not Valid"
Essentially here's what I'm trying to calculate:
If invoice number exists with Segment = Large Business AND Status = Approved then "Approved in Large Business"
Else if invoice number exists with Segment = Medium Business AND Status = Approved then "Approved in Medium Business"
Else if invoice number exists with Segment = Small Business AND Status = Approved then "Approved in Small Business"
Else "Duplicate or Not Valid"
Hi @Joshua Goolsby
Basically you can use LOD to do this like
{FIXED [Invoice Number] : MAX(
IF Segment = Large Business AND Status = Approved then "Approved in Large Business"
Else if Segment = Medium Business AND Status = Approved then "Approved in Medium Business"
Else if Segment = Small Business AND Status = Approved then "Approved in Small Business"
Else "Duplicate or Not Valid")}
it will give you only one result for each Invoice number.
Hope it helps,
Viviane
