topic Re: Search data of Table1 in Table2 in App Development

Search data of Table1 in Table2

DEMONIO_AZUL — Fri, 01 Nov 2024 02:09:16 GMT

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!

Re: Search data of Table1 in Table2

Tomm — Fri, 01 Nov 2024 06:07:02 GMT

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.

Re: Search data of Table1 in Table2

Kushal_Chawda — Fri, 01 Nov 2024 10:34:40 GMT

@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;

Re: Search data of Table1 in Table2

ramazanerduran — Fri, 01 Nov 2024 11:28:58 GMT

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

Re: Search data of Table1 in Table2

DEMONIO_AZUL — Fri, 01 Nov 2024 19:56:07 GMT

Thank you again!
This is what I needed!

Re: Search data of Table1 in Table2

DEMONIO_AZUL — Tue, 05 Nov 2024 22:01:12 GMT

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

Re: Search data of Table1 in Table2

Kushal_Chawda — Wed, 06 Nov 2024 12:28:36 GMT

@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;

Re: Search data of Table1 in Table2

DEMONIO_AZUL — Wed, 06 Nov 2024 13:10:35 GMT

This is AWESOME! Thanks again, @Kushal_Chawda !!