topic Re: select by substring in QlikView

select by substring

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

hi,

here my situation:

on the first column in table i have a list of items and in the second column the chain that join all items together.

for example, item "T.L20/34999000" have chain "1," and all items that begin with "1," are join with this ("S.LL20/20029000,172491H2,...").

what i want is when i select one item, have all its childs joined...i tried to have substring and in the list box is working, but only by select on chain value, not by item...

i hope to be undertand what i think...!

Re: select by substring

YoussefBelloum — Wed, 21 Feb 2018 16:04:48 GMT

Hi,

can you attach sample QVW ? or sample data ?

Re: select by substring

Anonymous — Wed, 21 Feb 2018 16:08:51 GMT

hi,

i attacched the example.

in second pic what i want to have by select an item..thank u!!

Re: select by substring

Anonymous — Fri, 23 Feb 2018 07:07:12 GMT

nobody??

Re: select by substring

sunny_talwar — Fri, 23 Feb 2018 12:32:06 GMT

I think you might need Hierarchy function easy and Efficient with example--- to get this working...

Re: select by substring

teempi — Tue, 27 Feb 2018 09:11:52 GMT

Hey,

Maybe you could extract all possible parent items in the load script and use the new parent field as a dimension in your chart? Check the attachment for an example.

-Teemu

Re: select by substring

Anonymous — Wed, 28 Feb 2018 07:06:41 GMT

hi,

yes! it s exatly what i want...i understood your script, now i m trying to use with lot of data, almost 100k rows...i hope will works fine...

many thank s! i ll give you asap a feedback!

Re: select by substring

teempi — Wed, 28 Feb 2018 11:15:43 GMT

Glad it worked

100k rows should probably be just fine. Even if there will be quite a few rows in the final table, it does allow you to write very simple expressions in your charts. In my opinion it's worth it.

-Teemu

Re: select by substring

Anonymous — Thu, 01 Mar 2018 11:24:09 GMT

so, just to know, in sql i do something like

select t1.c1,t1.queue2,t2.queue2,*

from #t2 t1

join #t2 t2

on t1.c1=t2.c1

and t1.queue2=SUBSTRING(t2.queue2,0,LEN(t1.queue2)+1)

order by t1.queue2

where #t2 could be in qv a previous sql dataset result.

for example

temp:

sql

select queue2

from table

could be possible to do it using resident table or is necessary use loop to do it?

Re: select by substring

Anonymous — Fri, 02 Mar 2018 11:02:38 GMT

i solved using temp table in sql server.

sql

select *

into #tmp

from "query"

;

ParentString:

sql

select t1.c1,t2.queue2,SUBSTRING(t2.queue2,0,LEN(t1.queue2)+1) q

from #tmp t1

join #tmp t2

on t1.c1=t2.c1

and t1.queue2=SUBSTRING(t2.queue2,0,LEN(t1.queue2)+1)

--order by t2.queue2

;