21 Replies Latest reply: Apr 25, 2018 11:00 AM by Sunny Talwar

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

• ###### 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)

• ###### 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'.

• ###### 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...

• ###### 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

• ###### Re: Value List within Set analysis

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

• ###### 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
• ###### 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?

• ###### 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

• ###### 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?

• ###### Re: Value List within Set analysis

That is correct. There are 40 total. 20 have from Group=1 and 20 from Group=2

• ###### Re: Value List within Set analysis

This doesn't help with the run time or this doesn't filter down Sub to just XY?

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

• ###### Re: Value List within Set analysis

And what is Group = 1 and Group = 2? I think there are so many different things going on, that I am unable to figure out the best way you can code your script...

• ###### Re: Value List within Set analysis

That's why I left that out originally. I can filter that on the front end. But if I could get just the subset XY then I would be happy.

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

Doesn't filter it down at all. It is still the full set of data

• ###### Re: Value List within Set analysis

XY is a text you are looking for in Sub, right? It needs to be within single quotes

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

Also, Sub is exactly XY or is it abcXYklm and you need to do a wildcard search like this?

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

• ###### Re: Value List within Set analysis

Yes it is exactly XY. I had it in single quotes but copying it here I forgot to add them. Thank you for all your help, I think I will just have to deal with the load time being what it is

• ###### Re: Value List within Set analysis

Ya unless you are able to provide me with a mocked up sample of your problem, I won't really know what is going on....

• ###### Re: Value List within Set analysis

One thing you can change is this

ChartTest:

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

'XY' as Sub,

'Test1' as TLPs

Resident DataFile

Where WildMatch("Landing Page Path", '/test1') and Sub = 'XY';

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

Concatenate (ChartTest)

'XY' as Sub,

'Test2' as TLPs

Resident DataFile

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

This will create a synthetic key, but it isn't a huge problem.... we can fix it.... but if this doesn't do anything for you... then I won't be able to help

• ###### Re: Value List within Set analysis

This didn't cut the load time down.

My biggest issues have been solved though, so again, thank you very much.

• ###### Re: Value List within Set analysis

If I understand it right than there should be a categorizing for [Landing Page Path], right? In this case I would try to avoid it within the UI and very probably not doing it within a valuelist() else rather with native fields even if it would be an island-table. But I think a script-solution with normal data-association should be preferable.

For this I believe Sunny's suggestion tends in the right direction whereby I think the multiple concatenation isn't necessary else the wildmatch for the categorizing could be applied directly within the load to create the matching-field maybe also as dual-field and/or with an additionally flag-fields. Further it might be an option to separate concatenated matching-values like "/test14/test9/test100b/test100c/test18" afterwards again with a subfield-loop.

Not quite clear is your field [Sub] and if there are duplicate fieldvalues of [Landing Page Path] in regard to this field. If no or if it's not too expensive to concatenate [Landing Page Path] and [Sub] you don't need a resident-load else you could just run this matching load over the fieldvalues which would by so a small field probably run under a second. Here the load-logic behind reading from fieldvalues: “Fastest” Method to Read max(field) From a QVD | Qlikview Cookbook.

- Marcus

• ###### Re: Value List within Set analysis

try this

for i=1 to 40

Let vTableName = 'Test' & \$(i);

\$(vTableName):

[Landing Page Path],

Sessions,

'Test' & \$(i) as TLPs

resident dUMMYtABLE

where WildMatch([Landing Page Path], '*/test' & \$(i));;

next i;

drop table dUMMYtABLE;