Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

agdgn123
New Contributor III

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
Esteemed Contributor II

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

Try

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

agdgn123
New Contributor III

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

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
Esteemed Contributor II

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

Can you post sample app or sample data?

vinieme12
Esteemed Contributor II

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

try

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

MVP
MVP

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

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)

agdgn123
New Contributor III

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

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?

agdgn123
New Contributor III

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

I am afraid it's still not working.

agdgn123
New Contributor III

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

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

MVP
MVP

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

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.

Community Browser