Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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...
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)
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'.
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...
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
Do you have the list if NewField values stored in an Excel file or something?
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 |
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?
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
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?