Solved: Select expression with CONTAIN

Hyman_van_Zyl · 05-15-2019 02:54 AM

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])))

Steve

Ah, try this:

ANY(
  SELECT(
    Categories[Category],
    AND(
      ISNOTBLANK([Key Word]),
      CONTAINS([_THISROW].[Description],[Key Word])
    )
  )
)

View solution in original post

Steve

Prefix [Description] with [_THISROW].:

ANY(SELECT(Categories[Category],CONTAINS([_THISROW].[Description],[Key Word])))

Hyman_van_Zyl

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

Steve

What does that mean?

Hyman_van_Zyl

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

Steve

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.

Hyman_van_Zyl

Yes and yes

Steve

Can you give me an example Description and Key Word values that don’t produce the expected result?

Hyman_van_Zyl

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.

Steve

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.

Hyman_van_Zyl

Done, its shared. Thank lots for looking into it. Its a mess still as its far from done.

Steve

Ah, try this:

ANY(
  SELECT(
    Categories[Category],
    AND(
      ISNOTBLANK([Key Word]),
      CONTAINS([_THISROW].[Description],[Key Word])
    )
  )
)

Hyman_van_Zyl

Beautifull, its working, thanks so much Steve

Steve

Happy to help. Please remember to remove my access to your app.

Hyman_van_Zyl

Will do

Jon_S

Hey @Steve

I’m having a similar difficulty with an expression.

Here’s my set up
TABLES

Users
Items
User Items
Outfits_MASTER
Outfit_DETAIL

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?

Steve

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.

Jon_S

@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

Steve

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.

Jon_S

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!

Jon_S

@Steve

Thanks for your epic reply!

Will try this out now