Value List within Set analysis

[Anonymous]

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!

[Anonymous]

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

[sunny_talwar]

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;

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar]

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*');

[Anonymous]

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

[sunny_talwar]

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

[sunny_talwar]

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

[marcus_sommer]

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

[krishna_2644]

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;