Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
intervigilium
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
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:

QlikCommunity_Thread_254588_Pic1.JPG

QlikCommunity_Thread_254588_Pic2.JPG

QlikCommunity_Thread_254588_Pic3.JPG

QlikCommunity_Thread_254588_Pic4.JPG

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

Thanks in advance