topic Re: How to loop a for comparing strings (match based) between two different fields , each coming from different table ? in QlikView

How to loop a for comparing strings (match based) between two different fields , each coming from different table ?

prahlad_infy — Tue, 05 Jul 2022 13:33:52 GMT

Hello Experts ,

As i am not good with looping , how to loop a for comparing strings between two different fields , each coming from different table ?

Note : Table 1 and table 2 has lots of rows , so joining with cartesian is ruled out .

table1:
LOAD * INLINE [

word, value
Ancient, k1
Torrent, k2
Vibrant, k3
Florent, k4
Succulent, k5
Ant, k6
];

table2:
LOAD * INLINE [
freetext
SOBBERANT
movietorrent
vibrantcolor
ancient roman church
Florent
plant based succuent
];

sample output views

Thank You All

Re: How to loop a for comparing strings (match based) between two different fields , each coming from different table ?

Or — Tue, 05 Jul 2022 13:37:07 GMT

I'd suggest looking into using the solution described in this thread for a partial text match lookup (mapping load):

https://community.qlik.com/t5/QlikView-App-Dev/Apply-Map-If-Wildmatch-to-find-text-within-text-string/td-p/131240

Re: How to loop a for comparing strings (match based) between two different fields , each coming from different table ?

prahlad_infy — Tue, 05 Jul 2022 17:12:19 GMT

Thank You Or for the reply .

I know this is weird , but i my requirement is to achieve result by looping (though not optimized way) .

That will help me on nested loop understanding .

So please help me with the solution using loop .

Re: How to loop a for comparing strings (match based) between two different fields , each coming from different table ?

marcus_sommer — Wed, 06 Jul 2022 15:50:48 GMT

In regard to learn to develop nested loops I suggest you look to any starter tutorials by any programming language because in Qlik it's logically the same and the syntactically differences are rather small.

Within Qlik it's rather seldom that nested loops on loaded data are needed and/or sensible - looping through folder/files and/or branching between various cases are more often but they are mostly outside from the data.

Especially by your mentioned large data-set it doesn't make sense because comparing each single value from one side to each value from the other side is quite near to a cartesian product ... Therefore I suggest to take a carefully look to the from @Or provided link.

- Marcus

Re: How to loop a for comparing strings (match based) between two different fields , each coming from different table ?

prahlad_infy — Thu, 07 Jul 2022 07:46:12 GMT

Hello marcus ,

Thanks for replying and mentoring over nuances .

I have gone through the link and it did solve my problem . I was just little over curious to know the flow of

loading two different dataset using loop (load within load , if i may call it ) , which i tried and didnt work .

Thank to @Or again .

Re: How to loop a for comparing strings (match based) between two different fields , each coming from different table ?

marcus_sommer — Thu, 07 Jul 2022 11:39:47 GMT

Without having seen your loop-script it's difficult to estimate what's going wrong.

Quite important is the understanding that you couldn't directly access any loaded data within the script - unless:

during another load
fetching a value with some special functions, like peek() or fieldvalue() whereby in many scenarios it's needed/required to assign them to variables, too

Peek() will run through the data-tables and fieldvalue() through the system-tables - whereby this mustn't mandatory be single-values else the values might be already merged with a concat-load or another loop-approach.

And in your case both sides from the data must be fetched - whereby it could become quite difficult to associate the comparing-results to the final/target-table - especially by using fieldvalue() which hasn't a direct-relation to a table.

This means the biggest challenge isn't the nested loop itself else what you need to do within it.

Further even with rather small data-sets you could easily create millions of iterations and therefore millions of times of fetching/comparing data and doing anything with it respectively I/O operations with some latency and it will probably need some milliseconds for each which could mean n hours by millions of iterations.

Therefore don't try to apply loops on data which aren't on the outside of loads.

- Marcus