I want to select a category based on if a key word in present in the description of a transaction. I added a key word column to the category table. I am trying to use this expression but it does not work. If I change the expression to a LIST it lists all the categories.
What am I missing?Thanks
Doesnt want to work, problem stays the same, if I make it LIST instead of Ref it list all the transactions and not only the ones with ey words in the description.
Any other ideas?
What does that mean?
So it becomes a ref list of all categories. [Category] is also the key ref of the Categories table
The Description column of the transaction row is of type Text.
The Key Word column of the Categories table is of type Text.
Yes and yes
Can you give me an example Description and Key Word values that don’t produce the expected result?
Description: Debit card purchase Pnp Fam Port Alfred 2x-X5-07T12:17:30 x6052
Key Word: Pnp
I tried an alternative way of achieving the same result by using SPLIT on the Description which produces a List of the words in the Description and then using the IN expression. I used this on the same example I provided above and it works generating the Category “Food”. My problem with this method is it limits me to adding only one word within the Description string as a Key Word. I would prefer to use sections.
Hmm… I can’t see why this wouldn’t work. Would you want me to examine your app? If so, you’ll need to share it with me: firstname.lastname@example.org. If not, you might need to reach out to email@example.com directly.
Done, its shared. Thank lots for looking into it. Its a mess still as its far from done.
Ah, try this:
ANY( SELECT( Categories[Category], AND( ISNOTBLANK([Key Word]), CONTAINS([_THISROW].[Description],[Key Word]) ) ) )
Beautifull, its working, thanks so much Steve
Happy to help. Please remember to remove my access to your app.
I’m having a similar difficulty with an expression.
Here’s my set up
- User Items
My objective is to find the outfit from Outfits_MASTER who’s Related Outfit_DETAILS match exactly the User’s Related User Items…
There’s 3 outfits in my database and i know that for the one user that i currently have, only 1 matches the items he’s got…
Here’s what i’ve got right now. And it works, but it brings up all the ‘potential’ outfits that contain at least one of his items…
SELECT( Outfits_MASTER[OUTFITMASTER_UID], CONTAINS( SELECT( User Items[ITEM_UID], [USER_UID] = [_THISROW].[USER_UID] ), [Related Outfit_DETAILs][ITEM_UID] ) )
I know that my thought process is roughly there. It’s almost like i need a CONTAINSALL() expression
SELECT( Outfits_MASTER[OUTFITMASTER_UID], ( SORT( SELECT( User Items[ITEM_UID], ([USER_UID] = [_THISROW].[USER_UID]) ) - LIST() ) = SORT( [Related Outfit_DETAILs][ITEM_UID] - LIST() ) ) )
SELECT(User Items[ITEM_UID], ...)gathers a list of values from the ITEM_UID column of the User Items table from rows that match the given criteria (
...; see (2)).
([USER_UID] = [_THISROW].[USER_UID])matches only rows where the USER_UID column value match the USER_UID column value of this row.
[Related Outfit_DETAILs][ITEM_UID]constructs a list of values from the ITEM_UID column of rows referenced by the list in the Related Outfit_DETAILs column value of the current row.
... - LIST()removes duplicate entries from the given list (
...; see (1) & (3)). List subtraction–even subtracting an empty list as done here–has the side-effect of removing duplicate entries from the result.
SORT(...)sorts the given list (
...; see (4)) in ascending order.
(... = ...)compares the two lists (originating from (1) & (3)), returning TRUE if identical or FALSE if not.
SELECT(Outfits_MASTER[OUTFITMASTER_UID], ...)gathers a list of values from the OUTFITMASTER_UID column from rows of the Outfits_MASTER table that match the given criteria (
...; see (6)).
When comparing lists, you should consider the possibility that a list may contain duplicate values. It’s not always possible to know how many duplicates might occur, and likely duplicates might occur in different numbers depending on how a list is generated. If you care only about the occurrence at all, but not about the number of recurrences, the recurrences (the duplicates) should be removed.
For instance, consider two lists: (A, B, B, B, C) and (A, B, C). Although both lists contain the same set of values, the first list contains three occurrences of B. Because the number of items differ, the lists aren’t strictly equal.
Also when comparing lists, you should consider the order of the items in a list. Just as the words “own” and “now” are different even though they contain the same letters, lists (A, B, C) and (C, A, B) are different because their identical items are ordered differently.
Thanks for your epic reply!
Will try this out now
@Steve this still doesn’t work. We’re close but it doesn’t work…
Here’s a screen shot of what i got
Smart Casual is CORRECT…
But very often there may be more than one exact match
USER has ITEMS: 1,2,3,4
O1 = ITEMS (1,2,3)
O2 = ITEMS (1,2,4)
The referenced list should provide me with O1 and of course O2
Hopefully you see where i’m going with this whole experiment
Ah, I see: you want outfits that the user has all of the components for? That’s actually easier:
SELECT( Outfits_MASTER[OUTFITMASTER_UID], ISBLANK( [Related Outfit_DETAILs][ITEM_UID] - SELECT( User Items[ITEM_UID], ([USER_UID] = [_THISROW].[USER_UID]) ) ) )
This one starts with the list of the outfit’s components (
[Related Outfit_DETAILs][ITEM_UID]) and removes from that the components the user already has (
SELECT(User Items[ITEM_UID], ...)), producing a list of components the user does not have. If that list is empty (
ISBLANK(...)), the user has all needed components.