Replace IF Condition here

[ychaitanya]

Hi All

I have 8B Records in the Fact table and i have the following expression for my Pivot table with Dimension1 , Dimension2, Dimension 3 and following expression

Line1 : If Dimension1='ABC' or Dimension1='BCD',(

Line2:  Sum({<Date={">=$(vFromDate_DET)<=$(vToDate_DET)"}>}Measure_Value)),

Line 3:  IF Dimension1='DEF',

Line4: Aggr( Sum({<Date={">=$(vFromDate_DET)<=$(vToDate_DET)"}>}Measure_Value),Dimension2,Dimension3)

As far as i know  Line 1 scans through 8B records and then scans to Line 2 and then filters the Dates according to the variables. -- > Performance is bad as it scans through entire Data

I need your help in writing the Date condition on the Line 1 using Set analysis - NOT IF Condition as it still performance poor.

Tried : 

IF Date >= vFROMDATE and Date<=vTODATE,

Sum(Measure Value)

This is very slow any pivot table is unable to load.

Can anyone help me putting the Condition with Date Subset on line1 so that my next if conditions run on that subset ( i have 30-40 lines of nested if's).

Thanks

Ch@i2

[Anil_Babu_Samineni]

Perhaps this
Sum({<Date={">=$(=vfromdate)<=$(=venddate)"}>}measure)

[marcus_sommer]

The if's are bad but more worse is the aggr - are you really sure that you need it? Especially if you consider some changes within the datamodel? The logic behind it is quite simple (even if it's not easy to implement): the bigger the dataset the simpler should be the expressions ...

- Marcus

[ychaitanya]

Hi Marcus

Thanks for the response - I have adhoc reporting and we are calculating the Peak values in the select dimensions by using max(aggr()) -> so we cannot go with aggregations on the Data model level .
Is there anyway you could suggest to get the subset of data according to the dates so that i can run the nested if's and aggr's ?

We have tried selecting a particular Date Selection and results are populating as the subset of the data is smaller - as we can't make the selections at accesspoint level . So we tried with IF Conditions to restrict to the particular date which is performance negative . Hence we are trying to get the solution other than if at the first place

[marcus_sommer]

There are several ways possible which might to improve the performance.

One would be to split the logic into several objects (each with only one dimension or at least lesser dimensions) so that there is no need to apply an aggr or the aggr might not so heavy. Also thinkable is to merge the dimensions within an additionally field.
This means mainly to consider which views on the data are really necessary to get useful insights and also to enforce some selections to restrict the dataset behind it - for example with a calculation condition of:

count(distinct Dim1 & Dim2 & Dim3) <= 1000

Another way is to include all fields within the used expression into a single table. If Qlik needs to collect the fields from multiple tables it will need more efforts to build the virtual table on which the calculations are performed - especially if the datamodel isn't a star-scheme else a heavily branched snow-flake or even some kind of link-table approach.

Further I could imagine that a logic which applied a single aggr-expression over all dimension might perform better as to apply multiple ones. This means to reduce as much as possible the calculations within the expressions and if there are really several needed then to use a:

pick(match(DimMerge, 'a', 'b', 'c', ...), expr1, expr2, expr3, ...)

instead of nested if-loops.

- Marcus

[ychaitanya]

Makes completely Sense Marcs - We have Adoc Report where we are showing Pivot table which can have 12 Dimensions and 1 Expression which i have mentioned above.

Do we have anything which can replace the following if to restrict the subset of the entire data set
If Date>=vfromdate and Date<=vtoDate

Even though i am putting this date filter on my set analysis , as i have if Dim1='A', Expr1 with set anlysis and date condition , if Dim1='B', Expr 2 with set anlysis and date condition -- This is not helping as i have the if condition which runs on Billions of records.

If i can find a way to restrict the data set at Line 1 as per the Date Variables my other code would work .

Thanks again for the help

Regards
Ch@i2

[marcus_sommer]

Like hinted in the previous answer I think that 12 selectable dimensions within a single table are too much and suggest to restrict it. In my adhoc-reports I use such restrictions by max. 3 vertical and 2 horizontal dimensions. In my opinion a logical distribution of the adhoc-views to multiple objects would be quite useful and not only for performance reasons else to provide a better usability.
For example by using multiple objects with only one or two dimensions and rather highly aggregated views with complex aggregations and if really multiple dimensions are needed for example for exports then using there only simple expressions like: sum/count(Field).

It wasn't mentioned in your descriptions but I assume that some of your if-branches cover the different possible dimensions within the aggr-dimensionslist. This might be shortened by querying them with getcurrentfield() and/or with getobjectfield() which could be used within a $-signexpansion like: $(=getobjectfield(0, 'CH01')).

In regard to: "If Date>=vfromdate and Date<=vtoDate" I doubt that this will improve the performance in any way comapred to the uasge within the set analysis. An alternatively might be to use an island-table for the date-selection instead of the variables and then using ... Date = p(DatesFromIslandTable) ... within the set analysis. Usually I wouldn't expect a significantly improvement from this but I never tested it and by a large dataset it might be noticable.

- Marcus