Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Compare text strings from 2 different tables and return value from table 2

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?

9 Replies
vinieme12
Champion III
Champion III

Try

AGGR(If(TitleKeyword=keywords,Sum(EtsySearchResults),'No Data'),ID,TitleKeyword,Keywords)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

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 "-".

vinieme12
Champion III
Champion III

Can you post sample app or sample data?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

try

Sum(Aggr(If(TitleKeyword=keywords,EtsySearchResult)),ID,TitleKeyword,Keywords)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
swuehl
MVP
MVP

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)

Anonymous
Not applicable
Author

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?

Anonymous
Not applicable
Author

I am afraid it's still not working.

Anonymous
Not applicable
Author

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

swuehl
MVP
MVP

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.