How MaxString works

[pascos88]

Hello,

I have a question, I have this expression:

if((Len (trim (maxSTRING({<CampaignYear_Fact = {'2017'}>} ValueReviewed))) <> 0) ,  maxSTRING({<CampaignYear_Fact = {'2017'}>} ValueReviewed) , 1)    --> as column FACT 2017

if((Len (trim (maxSTRING({<CampaignYear_Fact = {'2016'}>} ValueReviewed))) <> 0) ,  maxSTRING({<CampaignYear_Fact = {'2016'}>} ValueReviewed) , 1)        --> as column FACT 2016

and so on for 10 years..

Now since the app is really slow and is not possible to load all the data (cause time of calculation), I am wondering how this Maxstring works.  In my expression, the syntax is going to have a cross product with all rows that contain CampaignYear_Fact = 2017, 

or qlik recognized that this kind of calculation should be done per rows where for instance the name of the company  and the industry is the same?

Someone have idea how I can reduce the time?

I am using a normal table whit this structure:

Industry     companyName     Fact 2017  Fact2016  Fact2015 ..... Fact2007  

Thanks for any help

PS I am using qlik sense

[sunny_talwar]

Why don't you create a pivot table where you can add CampaignYear_Fact as a pivoted dimension... Is ValueReviewed a numeric or text field?

[pascos88]

cuold be a text or number

[sunny_talwar · Accepted solution]

I guess you can still use the similar idea... but use a CampaignYear_Fact as a dimension instead of creating 10 expressions

If(Len(Trim(MaxString({<CampaignYear_Fact = {">=2007<=2017"}>} ValueReviewed))) <> 0,  MaxString({<CampaignYear_Fact = {">=2007<=2017"}>} ValueReviewed), 1)

[pascos88]

Sunny Thanks for your answer, your solution giving to me just 1 column.. why? btw is good approach I guess

[sunny_talwar]

Did you add CampaignYear_Fact as dimension in a pivot table and then pivoted this dimension to go across instead of going down?

[pascos88]

ok, but without use a Pivot table... I cannot use the same syntax in a simple table?

[sunny_talwar]

you can... but then you will see CampaignYear_Fact going down and not across.... I suggest pivot table to get you this

Industry     companyName     Fact 2017  Fact2016  Fact2015 ..... Fact2007 

Without pivot table... you will get this

Industry companyName Year

...     ,....     Fact2017

...     ,....     Fact2016

...     ,....     Fact2015

...

...

...     ,....     Fact2007