Find fields which contains a part of string of other field

[intervigilium]

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

[MarcoWedel]

Hi,

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:

table1:
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,
    Description1
FROM [https://community.qlik.com/servlet/JiveServlet/download/1237555-271331/match_qlikview.xls] (biff, embedded labels, table is Sheet1$);

table2:
LOAD AutoNumber(Description2) as DescID, *
FROM [https://community.qlik.com/servlet/JiveServlet/download/1237555-271331/match_qlikview.xls] (biff, embedded labels, table is Blad1$);

hope this helps

regards

Marco

[intervigilium]

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:

MG

AA.10

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

[intervigilium]

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

Thanks in advance