Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
sunny_talwar

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

View solution in original post

21 Replies
sunny_talwar

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)

Anonymous
Not applicable
Author

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

sunny_talwar

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

Anonymous
Not applicable
Author

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

sunny_talwar

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

Anonymous
Not applicable
Author

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 FieldLanding 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
sunny_talwar

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?

Anonymous
Not applicable
Author

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

sunny_talwar

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?