topic Re: Value List within Set analysis in App Development

Value List within Set analysis

Anonymous — Thu, 19 Apr 2018 20:20:29 GMT

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

sunny_talwar — Thu, 19 Apr 2018 21:02:04 GMT

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

Anonymous — Thu, 19 Apr 2018 21:06:44 GMT

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

sunny_talwar — Thu, 19 Apr 2018 21:10:04 GMT

May be a link table then using a for loop

LinkTable:

LOAD [Landing Page Path],

'TEST1' as NewField

Resident ....

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

Concatenate (LinkTable)

LOAD [Landing Page Path],

'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

Anonymous — Mon, 23 Apr 2018 21:17:27 GMT

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

sunny_talwar — Mon, 23 Apr 2018 22:55:02 GMT

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

Re: Value List within Set analysis

Anonymous — Tue, 24 Apr 2018 19:33:05 GMT

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

sunny_talwar — Wed, 25 Apr 2018 13:25:21 GMT

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

Anonymous — Wed, 25 Apr 2018 13:38:56 GMT

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

ChartTest:

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

LOAD "Landing Page Path",

'Test1' as TLPs

Resident DataFile

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

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

Concatenate (ChartTest)

LOAD "Landing Page Path",

'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

sunny_talwar — Wed, 25 Apr 2018 13:41:53 GMT

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

Anonymous — Wed, 25 Apr 2018 13:53:34 GMT

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

Re: Value List within Set analysis

sunny_talwar — Wed, 25 Apr 2018 13:57:18 GMT

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

sunny_talwar — Wed, 25 Apr 2018 13:58:10 GMT

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

Anonymous — Wed, 25 Apr 2018 14:01:01 GMT

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

sunny_talwar — Wed, 25 Apr 2018 14:05:42 GMT

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

Anonymous — Wed, 25 Apr 2018 14:28:00 GMT

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

sunny_talwar — Wed, 25 Apr 2018 14:29:17 GMT

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

sunny_talwar — Wed, 25 Apr 2018 14:31:21 GMT

One thing you can change is this

ChartTest:

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

LOAD "Landing Page Path",

'XY' as Sub,

'Test1' as TLPs

Resident DataFile

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

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

Concatenate (ChartTest)

LOAD "Landing Page Path",

'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

marcus_sommer — Wed, 25 Apr 2018 14:50:44 GMT

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 _{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

Qrishna — Wed, 25 Apr 2018 14:51:18 GMT

try this

for i=1 to 40

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

$(vTableName):

load

[Landing Page Path],

Sessions,

'Test' & $(i) as TLPs

resident dUMMYtABLE

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

next i;

drop table dUMMYtABLE;