Hey Guys,
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
ANY(SELECT(Categories[Category],CONTAINS([Description],[Key Word])))
Solved! Go to Solution.
Ah, try this:
ANY(
SELECT(
Categories[Category],
AND(
ISNOTBLANK([Key Word]),
CONTAINS([_THISROW].[Description],[Key Word])
)
)
)
Prefix [Description]
with [_THISROW].
:
ANY(SELECT(Categories[Category],CONTAINS([_THISROW].[Description],[Key Word])))
Hey Steve,
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?
Thanks
What does that mean?
SELECT(Categories[Category],CONTAINS([_THISROW].[Description],[Key Word]))
So it becomes a ref list of all categories. [Category] is also the key ref of the Categories table
Please confirm:
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: sc2758g@gmail.com. If not, you might need to reach out to support@appsheet.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.
Will do
Hey @Steve
Iโm having a similar difficulty with an expression.
Hereโs my set up
TABLES
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
Any ideas?
Try this:
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.
@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
OUTFITS:
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.
Hey @Steve
This ALMOST works. If implemented as is, it produces all the outfitsโฆ but, if the column type is set to โtextโ it gives me the correct OUTFITMASTER_UIDs.
I simply created another VC with the following:
SELECT(
Outfits_MASTER[OUTFITMASTER_UID],
IN(
[OUTFITMASTER_UID],
LIST([Steves Select])
)
)
This works exactly how I imagined
Steve thanks a million! Drinks on me next time youโre in London!
User | Count |
---|---|
36 | |
33 | |
27 | |
23 | |
18 |