Records are increasing due to subfield function

[sunitha_chellaiah]

Hi, I want to create a new field in my existing table.

For this requirement I am using subfield function. This new field consist of 9 values.

subfield('Log1&Log2&Log3&Log4&Log5&Log6&Log7&Log8&Log9','&') as Log

Now the problem is while loading the data, records are increasing as well as it is taking lot of time to load.

My existing table has 455,534 records.

Previously I was using island table to create this field. But the performance was slow at the UI level.

So I am trying incorporate this field in the script level.

@sunny_talwar - Hope to get a help from you

Please help with solution to overcome this issue.

Thanks in advance.

[Oleg_Troyansky]

Hi Sunitha,

I can try to help, even though I'm not Sunny 🙂

The function Subfield() with 2 parameters, like yours, will parse the string by the specified qualifier and generate additional rows for every subfield - that's what you are experiencing, but it sounds like this is not the outcome you wanted to achieve.

What was your expectation for the Subfield function? If you simply want to concatenate the 9 parts of the Log, then you don't need the Subfield function. If your problem is different, then please clarify what it is.

Cheers,

[sunitha_chellaiah]

Hi Oleg,

Thanks your reply.

I want to create field as 

Log

Log0

Log1

Log2

Log3

Log4

Log5

Log6

Log7

Log8

Log9

That's why I am using this subfield script in my existing table. This field doesn't have any connection with the table. I just want to use this as a filter/ dimension. 

I don't want to create separate table/ island table to create this field as it is impacting generating time of my straight table. That's why I am trying to create this field in the actual table. 

This field has been used in nested if condition in measure

[marcus_sommer]

I think you should elaborate your source- and target-data as well as the aim with them in more details. Regarding to your description your code should exactly return your wanted field-values but I doubt that it would be expedient to do it within the facts by duplicating each record ten times.

In my experience are island-tables very useful to navigate or to filter anything within the UI - but not with nested if-loops which should be in general avoided else with set analysis, for example:

sum({< FactLogField = p(FilterLogField) >} AnyValue)

which would filter the facts to the selected filter-values against the system-tables and without creating cartesian products between the fact- and the filter-table.

[sunitha_chellaiah]

My expression is way too big due to which I was trying in the script.

Can you help to write the below mentioned expression in better way so that I can continue with island tables 

IF( GetFieldSelections(display_ou_forecasting) = 'All' OR GetSelectedCount(display_ou_forecasting) = 0
,

IF(GetSelectedCount(UOM) = 1 AND GetSelectedCount(LAGS) = 1,
IF( (
sum({$<lag_period={ "$(= GetFieldSelections(LAGS))" }>}
IF( GetFieldSelections(UOM) = 'CEU' , ABC,
IF( GetFieldSelections(UOM) = 'Selling' , DEF,
IF( GetFieldSelections(UOM) = 'Third' , HIJ,
IF( GetFieldSelections(UOM) = 'Fourth' , KLM,
)))) )
)
= 0 AND
sum(
IF( GetFieldSelections(UOM) = 'CEU' , 123,
IF( GetFieldSelections(UOM) = 'Selling' , 456,
IF( GetFieldSelections(UOM) = 'Third' , 789,
IF( GetFieldSelections(UOM) = 'Fourth' , 354,
)))) )
> 0,
'/0',
(
sum(
IF( GetFieldSelections(UOM) = 'CEU' , 123,
IF( GetFieldSelections(UOM) = 'Selling' , 456,
IF( GetFieldSelections(UOM) = 'Third' , 789,
IF( GetFieldSelections(UOM) = 'Fourth' , 354,
)))) )
/
sum({$<lag_period={ "$(= GetFieldSelections(LAGS))" }>}
IF( GetFieldSelections(UOM) = 'CEU' , ABC,
IF( GetFieldSelections(UOM) = 'Selling' , DEF,
IF( GetFieldSelections(UOM) = 'Third' , HIJ,
IF( GetFieldSelections(UOM) = 'Fourth' , KLM,
)))) )
)
),0)
// END ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

, IF( GetFieldSelections(display_ou_forecasting) = 'Under Forecasting'
,
IF(
(
sum( total <brand,affiliate>
IF( GetFieldSelections(UOM) = 'CEU' , 123,
IF( GetFieldSelections(UOM) = 'Selling' , 456,
IF( GetFieldSelections(UOM) = 'Third' , 789,
IF( GetFieldSelections(UOM) = 'Fourth' , 354,
))))
)
/
sum(total <brand,affiliate> {$<lag_period={ "$(= GetFieldSelections(LAGS))" }>}
IF( GetFieldSelections(UOM) = 'CEU' , ABC,
IF( GetFieldSelections(UOM) = 'Selling' , DEF,
IF( GetFieldSelections(UOM) = 'Third' , HIJ,
IF( GetFieldSelections(UOM) = 'Fourth' , KLM,
))))
)
)
> kpi_target_r_ou_high
,
// FORMULA ################################################################################################
IF(GetSelectedCount(UOM) = 1 AND GetSelectedCount(LAGS) = 1,
IF( (
sum({$<lag_period={ "$(= GetFieldSelections(LAGS))" }>}
IF( GetFieldSelections(UOM) = 'CEU' , ABC,
IF( GetFieldSelections(UOM) = 'Selling' , DEF,
IF( GetFieldSelections(UOM) = 'Third' , HIJ,
IF( GetFieldSelections(UOM) = 'Fourth' , KLM,
)))) )
)
= 0 AND
sum(
IF( GetFieldSelections(UOM) = 'CEU' , 123,
IF( GetFieldSelections(UOM) = 'Selling' , 456,
IF( GetFieldSelections(UOM) = 'Third' , 789,
IF( GetFieldSelections(UOM) = 'Fourth' , 354,
)))) )
> 0,
'/0',
(
sum(
IF( GetFieldSelections(UOM) = 'CEU' , 123,
IF( GetFieldSelections(UOM) = 'Selling' , 456,
IF( GetFieldSelections(UOM) = 'Third' , 789,
IF( GetFieldSelections(UOM) = 'Fourth' , 354,
)))) )
/
sum({$<lag_period={ "$(= GetFieldSelections(LAGS))" }>}
IF( GetFieldSelections(UOM) = 'CEU' , ABC,
IF( GetFieldSelections(UOM) = 'Selling' , DEF,
IF( GetFieldSelections(UOM) = 'Third' , HIJ,
IF( GetFieldSelections(UOM) = 'Fourth' , KLM,
)))) )
)
),0)
// END ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
, 0
)
,
IF( GetFieldSelections(display_ou_forecasting) = 'Over Forecasting'
,
IF(
(
sum( total <brand,affiliate>
IF( GetFieldSelections(UOM) = 'CEU' , 123,
IF( GetFieldSelections(UOM) = 'Selling' , 456,
IF( GetFieldSelections(UOM) = 'Third' , 789,
IF( GetFieldSelections(UOM) = 'Fourth' , 354,
))))
)
/
sum(total <brand,affiliate> {$<lag_period={ "$(= GetFieldSelections(LAGS))" }>}
IF( GetFieldSelections(UOM) = 'CEU' , ABC,
IF( GetFieldSelections(UOM) = 'Selling' , DEF,
IF( GetFieldSelections(UOM) = 'Third' , HIJ,
IF( GetFieldSelections(UOM) = 'Fourth' , KLM,
))))
)
)
< kpi_target_r_ou_low
,
// FORMULA ################################################################################################
IF(GetSelectedCount(UOM) = 1 AND GetSelectedCount(LAGS) = 1,
IF( (
sum({$<lag_period={ "$(= GetFieldSelections(LAGS))" }>}
IF( GetFieldSelections(UOM) = 'CEU' , ABC,
IF( GetFieldSelections(UOM) = 'Selling' , DEF,
IF( GetFieldSelections(UOM) = 'Third' , HIJ,
IF( GetFieldSelections(UOM) = 'Fourth' , KLM,
)))) )
)
= 0 AND
sum(
IF( GetFieldSelections(UOM) = 'CEU' , 123,
IF( GetFieldSelections(UOM) = 'Selling' , 456,
IF( GetFieldSelections(UOM) = 'Third' , 789,
IF( GetFieldSelections(UOM) = 'Fourth' , 354,
)))) )
> 0,
'/0',
(
sum(
IF( GetFieldSelections(UOM) = 'CEU' , 123,
IF( GetFieldSelections(UOM) = 'Selling' , 456,
IF( GetFieldSelections(UOM) = 'Third' , 789,
IF( GetFieldSelections(UOM) = 'Fourth' , 354,
)))) )
/
sum({$<lag_period={ "$(= GetFieldSelections(LAGS))" }>}
IF( GetFieldSelections(UOM) = 'CEU' , ABC,
IF( GetFieldSelections(UOM) = 'Selling' , DEF,
IF( GetFieldSelections(UOM) = 'Third' , HIJ,
IF( GetFieldSelections(UOM) = 'Fourth' , KLM,
)))) )
)
),0)
// END ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
, 0
)
)))

[marcus_sommer]

Something like this to choose the wanted field:

IF( GetFieldSelections(UOM) = 'CEU' , ABC,
IF( GetFieldSelections(UOM) = 'Selling' , DEF,
IF( GetFieldSelections(UOM) = 'Third' , HIJ,
IF( GetFieldSelections(UOM) = 'Fourth' , KLM,
))))

mustn't be queried multiple times within the dimensions/expression else it could be outsourced within a variable and then the variable is used instead. The performance-improvement is probably not very significantly but it will simplify the expression.

But there is more simplifying possible because all relevant information could be applied within the island-table because this mustn't be just a single column else they could contain multiple columns and even various grouping-logic. For example beside the field UOM could be a field ReturnField which then contained ABC/DEF/... and within the expressions you may use: $(=maxstring(ReturnField)) to get the related field in regard to the user-selection respectively the max/min if none selection exists or several ones were made.

Beside this I suggest to consider a re-design of the data-model because your measure-fields here are constructed as a crosstable which is quite often not the best solution especially in regard to handle all those fields within the script as well as within the UI. 

[Oleg_Troyansky]

I suspect that this formula works very slowly, considering the number of nested IF() statements that are repeated many times...

I'd recommend to consider creating associated fields in the data that would replace all of these IF statements. If you could link your field UOM with the desired values such as 'ABC', 'DEF', etc. - you could potentially avoid all the IF functions.

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!