Looking for a way to simplify a lookup table

[Vador98]

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:

This is what I'm trying to achieve:

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

Thanks!

Eric

[benakesh]

Hi @Vador98 ,

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

[Vador98]

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

[benakesh]

Hi @Vador98 , 

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

13

MS

2020

*

10001

FALSE

V-BRONZE

*

*

*

*

10001

FALSE

V-BRONZE

156

TN

2020

*

30003

FALSE

V-SILVER

6

OR

*

*

30003

FALSE

V-SILVER

6

OR

2020

*

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

[Vador98]

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!

[Vador98]

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

[Vador98 · Accepted solution]

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!