Skip to main content
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Find fields which contains a part of string of other field

Hi all,

I have 2 tables which i dont want to connect using applymap or join (would be nice to keep it seperated).

I have 1 table with a short description and 1 table with a second description and a value.

Description1 is leading and a shorter version of description 2 (sort of groupby?)

I want the sum of all values of description 2, which can be grouped by description 1.

Example: if description1 = 'AA' en description2 is 'AA.10.10.10' i want the sum of AA.10.10.10 (and for example AA.20.50)

Formule 2 sum( {$<Description2= {"AA*"}>} [Value] )

Formule 2 is not flexible..... so i changed it to : sum( {$<Description2= { (Description1 & "*"})>} [Value] )

That is not correct....

What can i do ?

(i tried also a lot with like match, wildmatch, etc).

3 Replies


I guess you should aim for a script solution that links your tables instead of keeping your data islands and defining the relation between the Description fields in chart expressions. You could also extract different subfields from your descriptions to be able to aggregate on different levels (like your set expression example suggests you want to achieve).

One way to do so might be:






LOAD *,    DescPath1&If(Len(DescPath2),'/'&DescPath2)&If(Len(DescPath3),'/'&DescPath3)&If(Len(DescPath4),'/'&DescPath4)&If(Len(DescPath5),'/'&DescPath5) as DescPath;

LOAD AutoNumber(Description1) as DescID,

    SubField(Description1,'.',1) as DescLvl1,

    SubField(Description1,'.',2) as DescLvl2,

    SubField(Description1,'.',3) as DescLvl3,

    SubField(Description1,'.',4) as DescLvl4,  

    SubField(Description1,'.',5) as DescLvl5,

    Left(Description1,Index(Description1&'.','.',1)-1) as DescPath1,

    Left(Description1,Index(Description1&'.','.',2)-1) as DescPath2,

    Left(Description1,Index(Description1&'.','.',3)-1) as DescPath3,

    Left(Description1,Index(Description1&'.','.',4)-1) as DescPath4,

    Left(Description1,Index(Description1&'.','.',5)-1) as DescPath5,


FROM [] (biff, embedded labels, table is Sheet1$);


LOAD AutoNumber(Description2) as DescID, *

FROM [] (biff, embedded labels, table is Blad1$);

hope this helps



Contributor III
Contributor III

Hi Marco,

Thank you for your comprehensive answer! Very clear how you fix this. Can learn a lot from it..

The difficulty is that i want, of  a list of difference levels, the sum of values.

I added 1 table to your file to explain. (name is visible).

so in 1 table for example the sum of:



including all lower levels as you made it.... Sounds your solution is very close, but i dont know how to fix that?

The list in the table (visible) will change very often (due to selection in original file).

Contributor III
Contributor III

Still trying a lot of things on this subject... Anyone who has an idea to solve this?

Thanks in advance