# Qlik Sense App Development

New Contributor

## Value List within Set analysis

I am trying to create a custom table. I've made the dimension

=ValueList('TEST1','TEST2')

The measures for this are all very similar, but distinctly different. In the final version there will be FORTY different values in the list, so I'm trying not to make 40 different formulas. (I've already made it, it runs very slowly)

Ideally I'd make a measure like this

Sum({<

[Landing Page Path]={"=\$(=pick(match(ValueList('TEST1','TEST2'),'TEST1','TEST2'),{"*/Test1*"},{"*/Test2*"}))"},

Date=

>}Sessions)

I can get it to work like this

pick(match(ValueList('TEST1','TEST2'),'TEST1','TEST2'),

Sum({<

[Landing Page Path]={"*/Test1*"}

Date=

>}Sessions)

,

Sum({<

[Landing Page Path]={"*/Test2*"}

Date=

>}Sessions))

but like I said as I start to grow to over 40 different values in the table, it REALLLLLLY slows down. I'm trying to simplify this formula

Thanks!

1 Solution

Accepted Solutions
MVP

## Re: Value List within Set analysis

May be a link table then using a for loop

'TEST1' as NewField

Resident ....

Where WildMatch([Landing Page Path], '*/Test1*');

'TEST2' as NewField

Resident ....

Where WildMatch([Landing Page Path], '*/Test2*');

and so on....

Again, don't right them 40 times, but may be create a for loop to do this...

21 Replies
MVP

## Re: Value List within Set analysis

What you are trying to do is not going to work.... its just not how set analysis really works... why don't you create a new field in the script from your [Landing Page Path] and use it as one of the dimensions in the chart?

Pick(WildMatch([Landing Page Path], '*/Test1*', '*/Test2*', .....), 'TEST1', 'TEST2',....) as NewField

and now use this as a dimension with this expression

Sum(Sessions)

New Contributor

## Re: Value List within Set analysis

I've thought about doing that but there is often some overlap.

For example one row in the table may be a combination of '/test2' + '/test6'.

MVP

## Re: Value List within Set analysis

May be a link table then using a for loop

'TEST1' as NewField

Resident ....

Where WildMatch([Landing Page Path], '*/Test1*');

'TEST2' as NewField

Resident ....

Where WildMatch([Landing Page Path], '*/Test2*');

and so on....

Again, don't right them 40 times, but may be create a for loop to do this...

New Contributor

## Re: Value List within Set analysis

Thanks, this already seems like a big improvement. Sorry for the late reply, but any way you can help with the for loop?

maybe a similar example

thanks again

MVP

## Re: Value List within Set analysis

Do you have the list if NewField values stored in an Excel file or something?

New Contributor

## Re: Value List within Set analysis

This is essentially what I need the table to look like (this is half of it) Each Line in the field is a sum of all the landing pages in the same row on the right. Some are completely independent. But others, like the 2nd last row, are a sum of original values and of other rows. I've written each one out individually(for just these 20) using the code you wrote above and the load time is about 8 minutes (I definitely need to get that lower)

 New Field Landing Page Path Test1 /test1 Test2 /test2 Test3 /test3a /test3b Test4 /test4 Test5 /test5 Test6 /test6 Test67 /test6 /test7 Test7 /test7 Test8 /test8a /test8b Test9 /test9 Test10 /test10 Test11 /test11a /test11b /test11c Test12 /test12a /test12b /test12c /test12d /test12e /test12f Test13 /test13 Test14 /test14 Test15 /test15 Test16 /test16 Test17 /test17 Test100 /test100a /test14 /test9 /test100b /test100c /test18 Test18 /test18
MVP

## Re: Value List within Set analysis

Well, I don't really think that the loop will be able to lower the load time.... there might be other things which can be done, but can you share the exact script you are running, might help fine tune it some other ways?

New Contributor

## Re: Value List within Set analysis

These are the first two, and it essentially repeats from there

ChartTest:

/***************************TEST1*******************************/

'Test1' as TLPs

Resident DataFile

Where WildMatch("Landing Page Path", '/test1');

/***************************Test2*******************************/

Concatenate (ChartTest)

'Test2' as TLPs

Resident DataFile

Where WildMatch([Landing Page Path], '/test2');

The only thing I can think to cut down the load time is that I only need the landing pages where Sub=XY but anytime I add this to the where clause, it does nothing. Like this:

Where WildMatch([Landing Page Path], '/test2') and Sub=XY;

Is there a way to take a subset of table before I load this?

I need the table as a whole elsewhere, only this chart I am trying to make needs this subset of data

Thanks

MVP

## Re: Value List within Set analysis

So, are you saying that you only care about Sub = XY? and nothing else while creating this new table? And how many concatenates do you have? 20? test1, test2,.... test20?