select by substring

[Anonymous]

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

[YoussefBelloum]

Hi,

can you attach sample QVW ? or sample data ?

[Anonymous]

hi,

i attacched the example.

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

[Anonymous]

nobody??

[sunny_talwar]

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

[teempi · Accepted solution]

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

[Anonymous]

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!

[teempi]

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

[Anonymous]

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?

[Anonymous]

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

;