Search data of Table1 in Table2

[DEMONIO_AZUL]

Hello there!

I have two tables, one called Packages and the other called Sales. Packages has 40 items in only one field and Sales has 10,000 items with different fields. 

For the sake of simplicity I will use “examples”, not the real, long field contents. 

Packages has these items: A1, X3, W6, H8.

Sales has one field (out of many) that often contains, among long strings of texts, the items listed in Packages and others, called Purchased, as follows:

SaleNo… Purchased

721.    A1,,,,

5.    X3;;; H8::: Z7 asdfghjkl Q4

36.    A1;; .. W6 fgdshg H8

807.    Sawtfcd hgfd kkklg sddfd

15. ssdfd X3 gfgdddljj poiigvf C5 H7 V2

205. H8.cvc hbbb A1

At load script I need to find and tag all Sales.Purchased the items where the items in Packages are found. It can be in one additional field or more than one, as long as I get, for instance, that SaleNo 5 contains X3 and H8, SaleNo 807 has no matches, SaleNo 205 contains A1 and H8, etc. And it is not a binary tag, I need to know which Package is in Purchased. 

My problem is to go through each item in Packages browsing all items in Sales to find out what Package matches within Purchased.

Any hints are appreciated. Thanks!

[Tomm]

You could force a join and have the each Package in [Packages] table joined to each Purchasaed in [Sales] table (40*10000 rows) then do a SubStringCount(Purchased, Packages) and cleanup your tables.

[Kushal_Chawda · Accepted solution]

@DEMONIO_AZUL  try below

map_items:
mapping LOAD Items,
                          '<' & Items & '>' as map_text
FROM Packages_table;

sales_table:
LOAD *,
          if(textbetween(mapsubstring('map_items',purchased),'<','>'),
          'Item found','Item not found') as Flag
FROM sales table;

[ramazanerduran]

Hi, 

Have you ever try to use `Exists()` func? Have a look at below doc:

• Exists - script function | Qlik Sense on Windows Help

Thanks,

Ramazan

[DEMONIO_AZUL]

Thank you again!
This is what I needed!

[DEMONIO_AZUL]

Hi @Kushal_Chawda,

I forgot to mention that this is what I needed...

The flag was not what I wanted. I left it here just to show I did test it... I needed one or two or as many dimensions (Found1, Found2...etc) as needed to show what packages were included in Purchased.

But definitely, without your help I wouldn't have been able to sort this out. Thanks again.

The desired result is the two columns shown:

The mapping LOAD effectively finds the occurrences of each Package in Purchased, but the textbetween function only find the first occurrence, so I created a second dimension.

Interesting would be to know if there's a way to get both Found1 and Found2 in just one dimension from the Mapped text that already has all occurrences between <>.

[Kushal_Chawda · Accepted solution]

@DEMONIO_AZUL  try below

packages:
mapping load packages,
             '<' & packages & '>' as max_text;
Load * Inline [
packages
A1
X3
W6
H8 ];

Sales:
Load *,
     MapSubString('packages',Purchased) as Mapped_Purchased,
     SubStringCount(MapSubString('packages',Purchased),'<') as text_cnt;
Load * Inline [
SaleN0, Purchased
721,    A1,,,,
5,    X3;;; H8::: Z7 asdfghjkl Q4
36,    A1;; .. W6 fgdshg H8
807,    Sawtfcd hgfd kkklg sddfd
15, ssdfd X3 gfgdddljj poiigvf C5 H7 V2
205, H8.cvc hbbb A1 ];

max_text_cnt:
Load max(FieldValue('text_cnt',RecNo())) as max_text_cnt
AutoGenerate FieldValueCount('text_cnt');

let vMax_Cnt = Peek('max_text_cnt');

Drop Table max_text_cnt;

formula:
Load Concat(formula &' '& if(No=$(vMax_Cnt),'','& '',''& '),chr(10)) as formula;
Load 'Textbetween(Mapped_Purchased,''<'',''>'',' & IterNo() & ')' as formula,
      IterNo() as No
AutoGenerate 1
while IterNo()<=$(vMax_Cnt);

let vTextBetweenFormula = Peek('formula');

Drop Table formula;

Drop Field text_cnt;

sales_final:
Load *,
     if(len(trim(Found))=0,'Not found',Found) as Found_Final;
Load *,
     Replace(trim(Replace($(vTextBetweenFormula),',',' ')),' ',',') as Found
Resident Sales;

Drop Table Sales;

Drop Fields Found,Mapped_Purchased;

[DEMONIO_AZUL]

This is AWESOME! Thanks again, @Kushal_Chawda !!