topic Re: Help required for code optimization?? in QlikView

Help required for code optimization??

rohitians — Wed, 30 Oct 2013 06:24:51 GMT

Hello Everyone,

I have this situation where in i have five bar charts.....i am picking up top five values from the list box which is sorted according to highest sales.....so i pick up first location name and display details for that location in first bar chart.....now thing is i have to declare five variables ....

vLocation1 =AGGR(IF(RANK(( SUM({$<Item_Ledger_Entry_Type_Code={0}>}Cost_Amt_Actual) + SUM({$<Item_Ledger_Entry_Type_Code={0}>}Cost_Amt_Expected) ) /100000)=1,Location_Name_Short),Location_Name_Short)

and in bar chart i am using calculated dimeson as follows..

=if(Location_Name_Short=vLocation1,Upper(Item_Name))

can declaring so many variables be avoided....

Thanks and Regards,

Rohit

Re: Help required for code optimization??

sujeetsingh — Wed, 30 Oct 2013 06:33:32 GMT

I am seeing only one variable there .

Well it is ok

Re: Help required for code optimization??

rohitians — Wed, 30 Oct 2013 06:37:15 GMT

Only the rank changes.. every thing else would remain the same..

Re: Help required for code optimization??

sujeetsingh — Wed, 30 Oct 2013 06:51:04 GMT

Ok give me sample please Rohit

Re: Help required for code optimization??

Wed, 30 Oct 2013 07:09:02 GMT

Hi,

You want to have seperate chart for each ranked location right, first chart to show item for rank 1 location, second chart to show rank 2 location, etc., In that case, I guess you hav to go with 5 set of variables.

Re: Help required for code optimization??

rohitians — Wed, 30 Oct 2013 07:14:40 GMT

I have 5 set of variables already i want to avoid those five so is der any way i can do that.....

Re: Help required for code optimization??

jonathandienst — Wed, 30 Oct 2013 07:23:36 GMT

You can use a parameterised variable like this:

Set vLocation1 = AGGR(IF(RANK((SUM({$<Item_Ledger_Entry_Type_Code={0}>}Cost_Amt_Actual) + SUM({$<Item_Ledger_Entry_Type_Code={0}>} Cost_Amt_Expected)) /100000)=$1,Location_Name_Short),Location_Name_Short)

And then use this it like this:

=if(Location_Name_Short='$(vLocation1(1))',Upper(Item_Name))

=if(Location_Name_Short='$(vLocation1(2))',Upper(Item_Name))

...

HTH

Jonathan

Re: Help required for code optimization??

rohitians — Wed, 30 Oct 2013 07:39:28 GMT

Thank you Jonathan I thaught of this but it is showing syntax error... at $1..

Re: Help required for code optimization??

jonathandienst — Wed, 30 Oct 2013 09:13:21 GMT

Make sure you are using Set and not Let to define Location1. I didnt test so you might need to play with the = signs to get it to work.

Set vLocation1 = =AGGR(IF(RANK((SUM({$...

Test the macro expansion of Location1 in a textbox:

=$(Location1(1))

=$($(Location1(1)))

HTH

Jonathan