how Can I use field expression in variable input function of Qlikview.

[kiara]

=concat(if(firstsortedvalue(distinct
(([DLA 20 Assessment scores.1. Health Practices]+
[DLA 20 Assessment scores.2. Housing Stability and Maintenance]+
[DLA 20 Assessment scores.3. Communication]+
[DLA 20 Assessment scores.4. Safety]+
[DLA 20 Assessment scores.5. Managing Time]+
[DLA 20 Assessment scores.6. Managing Money ("stuff)]+
[DLA 20 Assessment scores.7. Nutrition]+
[DLA 20 Assessment scores.8. Problem Solving]+
[DLA 20 Assessment scores.9. Family Relationships]+
[DLA 20 Assessment scores.10. Alcohol/ Drug Use]+
[DLA 20 Assessment scores.11. Leisure]+
[DLA 20 Assessment scores.12. Community Resources]+
[DLA 20 Assessment scores.13. Social Network]+
[DLA 20 Assessment scores.14. Sexual health and sexuality]+
[DLA 20 Assessment scores.15. Productivity]+
[DLA 20 Assessment scores.16. Coping Skills]+
[DLA 20 Assessment scores.17. Behavior Norms]+
[DLA 20 Assessment scores.18. Personal Care, Hygiene]+
[DLA 20 Assessment scores.19. Grooming]+
[DLA 20 Assessment scores.20. Dress])/20),-[DLA 20 Assessment scores.Date of Assessment],1) >= 6.0,'Level G',|
if(firstsortedvalue(distinct
(([DLA 20 Assessment scores.1. Health Practices]+
[DLA 20 Assessment scores.2. Housing Stability and Maintenance]+
[DLA 20 Assessment scores.3. Communication]+
[DLA 20 Assessment scores.4. Safety]+
[DLA 20 Assessment scores.5. Managing Time]+
[DLA 20 Assessment scores.6. Managing Money ("stuff)]+
[DLA 20 Assessment scores.7. Nutrition]+
[DLA 20 Assessment scores.8. Problem Solving]+
[DLA 20 Assessment scores.9. Family Relationships]+
[DLA 20 Assessment scores.10. Alcohol/ Drug Use]+
[DLA 20 Assessment scores.11. Leisure]+
[DLA 20 Assessment scores.12. Community Resources]+
[DLA 20 Assessment scores.13. Social Network]+
[DLA 20 Assessment scores.14. Sexual health and sexuality]+
[DLA 20 Assessment scores.15. Productivity]+
[DLA 20 Assessment scores.16. Coping Skills]+
[DLA 20 Assessment scores.17. Behavior Norms]+
[DLA 20 Assessment scores.18. Personal Care, Hygiene]+
[DLA 20 Assessment scores.19. Grooming]+
[DLA 20 Assessment scores.20. Dress])/20),-[DLA 20 Assessment scores.Date of Assessment],1) >= 5.1,'Level 1',|
if(firstsortedvalue(distinct
(([DLA 20 Assessment scores.1. Health Practices]+
[DLA 20 Assessment scores.2. Housing Stability and Maintenance]+
[DLA 20 Assessment scores.3. Communication]+
[DLA 20 Assessment scores.4. Safety]+
[DLA 20 Assessment scores.5. Managing Time]+
[DLA 20 Assessment scores.6. Managing Money ("stuff)]+
[DLA 20 Assessment scores.7. Nutrition]+
[DLA 20 Assessment scores.8. Problem Solving]+
[DLA 20 Assessment scores.9. Family Relationships]+
[DLA 20 Assessment scores.10. Alcohol/ Drug Use]+
[DLA 20 Assessment scores.11. Leisure]+
[DLA 20 Assessment scores.12. Community Resources]+
[DLA 20 Assessment scores.13. Social Network]+
[DLA 20 Assessment scores.14. Sexual health and sexuality]+
[DLA 20 Assessment scores.15. Productivity]+
[DLA 20 Assessment scores.16. Coping Skills]+
[DLA 20 Assessment scores.17. Behavior Norms]+
[DLA 20 Assessment scores.18. Personal Care, Hygiene]+
[DLA 20 Assessment scores.19. Grooming]+
[DLA 20 Assessment scores.20. Dress])/20),-[DLA 20 Assessment scores.Date of Assessment],1) >= 4.1,'Level 2',|
if(firstsortedvalue(distinct
(([DLA 20 Assessment scores.1. Health Practices]+
[DLA 20 Assessment scores.2. Housing Stability and Maintenance]+
[DLA 20 Assessment scores.3. Communication]+
[DLA 20 Assessment scores.4. Safety]+
[DLA 20 Assessment scores.5. Managing Time]+
[DLA 20 Assessment scores.6. Managing Money ("stuff)]+
[DLA 20 Assessment scores.7. Nutrition]+
[DLA 20 Assessment scores.8. Problem Solving]+
[DLA 20 Assessment scores.9. Family Relationships]+
[DLA 20 Assessment scores.10. Alcohol/ Drug Use]+
[DLA 20 Assessment scores.11. Leisure]+
[DLA 20 Assessment scores.12. Community Resources]+
[DLA 20 Assessment scores.13. Social Network]+
[DLA 20 Assessment scores.14. Sexual health and sexuality]+
[DLA 20 Assessment scores.15. Productivity]+
[DLA 20 Assessment scores.16. Coping Skills]+
[DLA 20 Assessment scores.17. Behavior Norms]+
[DLA 20 Assessment scores.18. Personal Care, Hygiene]+
[DLA 20 Assessment scores.19. Grooming]+
[DLA 20 Assessment scores.20. Dress])/20),-[DLA 20 Assessment scores.Date of Assessment],1) >= 3.1,'Level 3',|
if(firstsortedvalue(distinct
(([DLA 20 Assessment scores.1. Health Practices]+
[DLA 20 Assessment scores.2. Housing Stability and Maintenance]+
[DLA 20 Assessment scores.3. Communication]+
[DLA 20 Assessment scores.4. Safety]+
[DLA 20 Assessment scores.5. Managing Time]+
[DLA 20 Assessment scores.6. Managing Money ("stuff)]+
[DLA 20 Assessment scores.7. Nutrition]+
[DLA 20 Assessment scores.8. Problem Solving]+
[DLA 20 Assessment scores.9. Family Relationships]+
[DLA 20 Assessment scores.10. Alcohol/ Drug Use]+
[DLA 20 Assessment scores.11. Leisure]+
[DLA 20 Assessment scores.12. Community Resources]+
[DLA 20 Assessment scores.13. Social Network]+
[DLA 20 Assessment scores.14. Sexual health and sexuality]+
[DLA 20 Assessment scores.15. Productivity]+
[DLA 20 Assessment scores.16. Coping Skills]+
[DLA 20 Assessment scores.17. Behavior Norms]+
[DLA 20 Assessment scores.18. Personal Care, Hygiene]+
[DLA 20 Assessment scores.19. Grooming]+
[DLA 20 Assessment scores.20. Dress])/20),-[DLA 20 Assessment scores.Date of Assessment],1) < 3.0,'Level 4','-'|)))))

How Can I use this expression for creating a dropdown list like , Level1, Level2 ................so on in variable input function of Qlikview.

[TcnCunha_M · Accepted solution]

Hi,

you can't move all this logic into Script?

Like create Inline table like
Load * inline [
Expresion , Type level 

[Your expression here] ,  Level 0

];

then you can use the Type level as your filter field 🙂

other wise you have to use aggr

[marcus_sommer]

Your source has a crosstable data-structure which has in the most scenarios massive disadvantages in the handling (in script as well as within the UI). Therefore you should transform them into "normal" records and applying then all wanted logic on this data-set. Backgrounds and how to, see:

The Crosstable Load - Qlik Community - 1468083

[kiara]

No, I don't have acces to Scripting. Can you tell  how I have to use Aggr ?

Thank you!

[marcus_sommer]

That you have no script-access doesn't mean that it shouldn't solved there. Just open a request by the guys which are responsible for the ETL part and if they ask why that this kind of data-structure is not suitable of being processed in any UI.