select in field with concat statement

[Stefan_Weber]

Hello Qlikview community,

I am trying to solve the following problem, but keep tumbling over my feet while I am doing it. Maybe you are able to help me.

I am trying to select fieldfalues by clicking on a button. I have selected select in field function and my Fieldname ist AssetID

Now I have 4 criterias whereof 3 need to be met

1) Good data QUality, which consisty out of 2 variables

2) Date of last data entry is not more than variable days in the past

3) Asset is explained sufficiently

4) Sufficiently explained

I am trying to get the AssetIDs with the concat statement and select them by clicking on the button.

='('&Concat(
sum({<Assetid (
if($(FormelAE)> AE_Data_GoodRage_min and $(FormelAE) < AE_Data_GoodRage_max,1,0)+
if($(maxSelectedDate)-max(ProductionDate)<AE_Data_GoodPeriod,1,0) +
if( sum( {$<AEMainCategoryKey={'Unexplained'}>} DurationDayFrac*FixedCashCostAE)/sum( DurationDayFrac*FixedCashCostAE)<(1-AE_Data_Good_Explained),1,0) +
if( sum( {$<AEMainCategoryKey={'Undeclared'}>} DurationDayFrac*FixedCashCostAE)/sum( DurationDayFrac*FixedCashCostAE)<(1-AE_Data_Good_Declared),1,0))
={">=3"}>})
distinct AssetId, '|')&')'

Unfortunately its not doing what it should and after a couple of hours looking I am getting blind.

I would appreciate your help. Do you see where I have the error ?

Thanks

Stefan

[Stefan_Weber]

Hello Qlikview Community,

any ideas are highly welcome. I am still stuck with this topic.

Thanks

Stefan

[marcus_sommer]

I think there are multiple issues with your approach. First of all you are nesting several aggregations without the use of aggr() - an aggregation needs always a definite dimensional context. Take a look here to comprehend the logic: Calculated-Dimensions.

Further your used syntax of the set analysis which includes the if-loops isn't correct because in the end it must be:

{< Field = {'value'}>} /* or a list of values - it might be also the result of any variable/expression */

or

{< Field = {"=AnyCondition=true()"}>}

The next point are the variables - if they are expressions they might be also wrapped with aggr() - which must return a valid result which also may fail if the variables contain comments or return formatted results.

The easiest way to develop such complex logic is to use a table-chart with the appropriate dimensions and to apply each calculation-part as a single expression and then to check if they return the expected results. If this worked they could be merged again to more complex constructs.

- Marcus

[Stefan_Weber]

Hello Marcus,

thanks for your contribution, I will try to develop it as you described, thanks also for the link. It was really helpful.

Regards

Stefan

[Stefan_Weber]

Hello Qlikview Community,

I have made some progress in regard to the Formula. The only thing where I am currently stumbling over still, ist that it nw takes every AssetID in the concat statement. Can someone help me so that only those AssetIDs get taken which fullfil the if statement?

=
'('&Concat(
{<Assetid={"=
(
if($(FormelAE)> AE_Data_GoodRage_min and $(FormelAE) < AE_Data_GoodRage_max,1,0)+
if($(maxSelectedDate)-max(ProductionDate)<AE_Data_GoodPeriod,1,0) +
if( sum( {$<AEMainCategoryKey={'Unexplained'}>} DurationDayFrac*FixedCashCostAE)/sum( DurationDayFrac*FixedCashCostAE)<(1-AE_Data_Good_Explained),1,0) +
if( sum( {$<AEMainCategoryKey={'Undeclared'}>} DurationDayFrac*FixedCashCostAE)/sum( DurationDayFrac*FixedCashCostAE)<(1-AE_Data_Good_Declared),1))
>=3
=True()"}>}
distinct AssetId, '|')&')'

I have been trying to use the statement Marcel wrote about {< Field = {"=AnyCondition=true()"}>} but apparentely I am still doing something wrong.

Any help is highly appreciated.

Thanks

Stefan

[marcus_sommer]

Sorry, that I didn't make it clearer. My example of:

{< Field = {"=AnyCondition=true()"}>}

wasn't meant literally else it should just show the logic that within the {...} a condition could be applied which returned TRUE or FALSE whereby Qlik treats each number as TRUE which is unequal as 0. This means =true() simply added to your condition is wrong and couldn't work. In principle your Condition1 + Condition2 + ... >=3 is right but you used the + operator and your last condition has no else branch - if any of those conditions fail and return no numeric result or NULL the whole will return NULL. Better in such cases is the use of range-functions, for example: rangesum(condition1, condition2, ...).

Did you check separately that each single calculation/condition worked? Beside this I suggest to check if some of these conditions could be (partly) moved into the script because it would simplify your UI logic.

- Marcus

[Brett_Bleess]

Stefan, here is the base URL to the Design Blog area, this will allow you to search the entire area, there are hundreds of posts there on how to do different things, so likely a good place to search for details on things related to Marcus' posts, hopefully this along with what Marcus has provided will help you get things going:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

I generally use a single keyword for starters and see what pops up in the search and then try to figure out if I am on the right track or not...

Regards,
Brett

[Stefan_Weber]

Hello Qlikview community, 

thanks for the help and the links, I was quite busy trying them out, but unfortunately I was not able to get it to work. I am summarizing what I have done, maybe you have another hint on how I may achieve my goal. 

I have now managed to get the values depending on the "OK" criteria with this formula: 

if((
if($(FormelAE)> AE_Data_GoodRage_min and $(FormelAE) < AE_Data_GoodRage_max,1,0)+
if($(maxSelectedDate)-max(ProductionDate)<AE_Data_GoodPeriod,1,0) +
if( sum( {$<AEMainCategoryKey={'Unexplained'}>} DurationDayFrac*FixedCashCostAE)/sum( DurationDayFrac*FixedCashCostAE)<(1-AE_Data_Good_Explained),1,0) +
if( sum( {$<AEMainCategoryKey={'Undeclared'}>} DurationDayFrac*FixedCashCostAE)/sum( DurationDayFrac*FixedCashCostAE)<(1-AE_Data_Good_Declared),1))
>=3,AssetId)

As long as I have the AssetId in the dimension, this delivers nice results, only those where the criterias are matched, I see the AssetId. Now if i remove the AssetId criteria and put it in a textfield I have tried to include the AssetId somehow as "virtual dimension" but haven´t been aber to figure out how to do it. 

Closest similar thing that I found in another post was this: 

=CONCAT(IF(aggr(Rank(sum(Value)),MyColumn)<=3,MyColumn),',')

I tried to remove the rank and said if the sum in the calculated field is >= 3 but also this did not lead to the desired result. 

I would be garteful if you had any other insight, otherwise again thanks for your contributions. 

Regards

Stefan

[marcus_sommer · Accepted solution]

If your expression worked like expected within a chart with AssetId as dimension you could just wrap them with the aggr() like:

concat(aggr(YourWorkingExpression, AssetId, ', ')

- Marcus

[Stefan_Weber]

Hello Marcus, 

unfortunately that does not deliver any result. I don´t know what I am doing wrong, slowly getting frustrated. 😉

Thanks for the help.