Looking for a way to simplify a lookup table

I have a very large workflow that creates a lookup table. I've gotten it to the point where it has all the details I need to find any value I want, however, it's nearly 100k records in size. The system it is to be used on allows for a default value of "*" that will catch any values after that point. The lookup table could have various numbers of columns - typically 5 or 6. I need to figure out how I cycle through the columns and determine which rows can be eliminated by using a default character. This is an example of what I have:

Lookup Table Start.jpg

This is what I'm trying to achieve:

Lookup Table Finish.jpg

I've gone about this several ways, but keep hitting walls. Any suggestions?

Thanks!

Eric

Dynamic Processing

Accepted answers

NothingIsImpossible

Hi @benakesh ,

Thank you for your help in your attempts for a solution! I have finally found a way to do what I'm looking for. I'm sure it's not the best approach, but it appears to be doing the job. I'm certainly welcome to a more dynamic approach, or a cleaner approach with less tools. I figured that it's unlikely that I'll have more than 6 keys, so I just built out 6 as my default. I guess if I run into something with more, I'll build out another key. I've attached my solution. If anyone has efficiencies that can be made, I would welcome the suggestions!

Thanks!

Sample Lookup Simplification logic.yxmd

All comments

benakesh

Hi @NothingIsImpossible ,

Couple of transpose and cross tab's produced the result . Let us know if it works for full data .

lookup1.yxmd

NothingIsImpossible

It doesn't appear that this solution will work for me. This causes values to go missing, and also creates nested asterisks that aren't allowed by our system. I've provided a more accurate data sample and your sample workflow here. I don't have the simplified version of the provided data, but I'm pretty sure you understand the goal.

Edit: Added expected results

Sample Lookup Simplification logic.yxmd

benakesh

Hi @NothingIsImpossible ,

The first 3 columns have no * in output . Does this mean first 3 columns are the keys . Transpose requires key columns .

Input

LookupValue

True?

Metal

Tier

State

Year

Zip

output

LookupValue

True?

Metal

Tier

State

Year

Zip

10001

FALSE

V-BRONZE

2020

10001

FALSE

V-BRONZE

10001

FALSE

V-BRONZE

156

2020

30003

FALSE

V-SILVER

30003

FALSE

V-SILVER

2020

I am trying to test the logic for these 2 cases . Can you explain why for 30003 the year changed to * .

NothingIsImpossible

Since there are two rows that have False and V-SILVER, we have to go deeper to determine which value is correct. Please see the table below:

Lookup Value	True?	Metal	Tier	State	Year	Zip
00030003	FALSE	V-SILVER	6	OR	*	*
00040004	FALSE	V-SILVER	6	MN	*	*

Since the rows start diverging at the State level, that's how deep we need to go to figure out which value we want. When this is passed into the destination system, all these values will be concatenated together with pipes (FALSE|V-SILVER|6|...). Hopefully that helps!

Thanks again!

NothingIsImpossible

I'm not sure that it will help, but I've written the simplification logic in Visual Basic previously. Here's that code, just in case

    'Determine how many keys we're working with.
    strSQL = "select top 1 len(@LUT.Keyvalue)-len(replace(@LUT.KeyValue,""|"",""""))+1 as total FROM @LUT"
    strSQL = Replace(strSQL, "@LUT", LUT)
    qd.SQL = strSQL
    Set rsl = qd.OpenRecordset()
    Total = rsl!Total
    rsl.Close
    Set rsl = Nothing
    
    'Go through each key value
    'Build temp table of all values that need to be converted.
    'Process will need to be repeated for as many values are in the key structure.  After structure has been fixed, more updates may be possible.
    For i = 2 To Total  'i represents which key value we're currently looking at
        If i = Total Then
            strSQL = "SELECT left(A.[KeyValue],GetXPosition(A.[KeyValue],@i))&""*"" as KeyValue, A.[LookupValue], count(a.[LookupValue]) As Total INTO tmpKeyCounts FROM @LUT A Group by left(A.[KeyValue],GetXPosition(A.[KeyValue],@i))&""*"",A.[LookupValue]"
        Else
            Asterisks = ""
            For j = i To Total
                Asterisks = Asterisks & "|*"
            Next j
            strSQL = "SELECT left(A.[KeyValue],GetXPosition(A.[KeyValue],@i)-1) & """ & Asterisks & """ As KeyValue, A.[LookupValue], count(A.[LookupValue]) As Total INTO tmpKeyCounts FROM @LUT A Group by left(A.[KeyValue],GetXPosition(A.[KeyValue],@i)-1) & """ & Asterisks & """,a.[LookupValue]"
        End If
        
        'Don't consolidate if the prior key value was the Benefit Plan Year (preserves zip codes)
        If Structure(i - 2) = "<BenefitPlanYear>" Then strSQL = "Select KeyValue, LookupValue, 1 as Total INTO tmpKeyCounts from @LUT"
        
        'Determine how many duplicate rows can be elimintated.
        strSQL = Replace(strSQL, "@LUT", LUT)
        strSQL = Replace(strSQL, "@i", i)
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        
        'Determine Keys to be consolidated
        strSQL = "SELECT A.KeyValue, A.LookupValue, A.Total INTO tmpDeleteKeys FROM tmpKeyCounts AS A where A.Total > 1 and not exists (Select 1 from tmpKeyCounts as B where B.KeyValue=A.KeyValue and A.LookupValue <> B.LookupValue)"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        
        'Actually delete the duplicate rows.  We will re-insert the one from the tmpDeleteKeys table
        strSQL = "Delete from @LUT A Where exists (select 1 from tmpDeleteKeys B where left(A.[KeyValue],GetXPosition(A.[KeyValue],@i)) = left(B.[KeyValue],GetXPosition(B.[KeyValue],@i)) and A.LookupValue = B.LookupValue)"
        strSQL = Replace(strSQL, "@LUT", LUT)
        strSQL = Replace(strSQL, "@i", i)
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        
        'Now Insert the rows back from the tmpDeleteKeys table
        strSQL = "Insert into @LUT Select KeyValue,LookupValue From tmpDeleteKeys"
        strSQL = Replace(strSQL, "@LUT", LUT)
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        
        DeleteTable "tmpKeyCounts"
        DeleteTable "tmpDeleteKeys"
    Next i

NothingIsImpossible

Hi @benakesh ,

Thanks!

Sample Lookup Simplification logic.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703