Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have 2 tables with texts strings and i am trying to compare / find matches between the 2 in order to retrieve additional data that relates to one of them.
Table1 has 2 columns: id, TitleKeyword
Each ID has more than one TitleKeyword entry.
Table2 includes 2 columns: Keywords, EtsySearchResults.
I want to build a simple table that includes the 2 columns from table 1 + a third column that returns EtsySearchResults value whenever TItleKeyword=Keywords.
I tried using this IF statement:
If(TitleKeyword=keywords,Sum(EtsySearchResults),'No Data')
But oddly it only returns a value if I filter on the Keyword term.
For example, if i filter on Keyword black tote bag on table 2, i get the search results on table 1 (see below):
If i release the filter, the third column in table 1 says "No Data" (see below)
Why is that?
How do I get the EtsySearchResults to show against each TitleKeyword that has an equivalent keyword on table 2?
Try
AGGR(If(TitleKeyword=keywords,Sum(EtsySearchResults),'No Data'),ID,TitleKeyword,Keywords)
Thanks Vineeth, I tried your syntax but i am afraid the result is the same. if i filter for the Keyword term the data shows. if i don't filter for it, the column returns "-".
Can you post sample app or sample data?
try
Sum(Aggr(If(TitleKeyword=keywords,EtsySearchResult)),ID,TitleKeyword,Keywords)
Have you tried renaming Keywords field in your data model to TitleKeyword using
LOAD
Keywords as TitleKeywords,
EtsySearchResult
FROM ....;
in the load script.
Then both tables should be linked by TitleKeywords. (Another approach would be to e.g. JOIN the tables with aliased key field)
Then you should be able to use your two dimensions and expression
=Sum(EtsySearchResult)
or any other aggregation that returns what you want to see, maybe just Only(EtsySearchResult)
Hi Stefan ,
I thought about renaming the Keywords field to TitleKeyword but these 2 fields derive from 2 different sources (product page and search statistics). If i give them the same field name, how will i be able to how would I be able to differentiate between their sources?
I am afraid it's still not working.
Sample data - table 1:
id ,TitleKeyword
484851322, big black tote
253926487, black canvas tote bag
253848667, black tote bag
279549112, black tote bag
253926487, large canvas tote bag
Sample data- table 2:
keywords, EtsySearchResults
black bag, 147,829
black canvas bag, 7,363
black canvas tote, 4,303
black canvas tote bag, 4,272
black shoulder bag, 18,597
black tote bag, 22,745
oversized black tote, 468
unique black canvas tote, 197
Required result table:
id TitleKeyword EtsySearchResults
484851322 big black tote no data
253926487 black canvas tote bag 4272
253848667 black tote bag 22745
279549112 black tote bag 22745
253926487 large canvas tote bag no data
If you just link the tables by same Keyword field, you will still be able to differentiate about the sources, because of the relation of the key to other fields in the two tables that are always filled with values, like Id for the first table or EtsySearchresult for the second table. Like
=Count({<Id = {"*"}>} DISTINCT TitleKeyword)
to count key words only in table 1.