topic Re: FOR Each FieldValueList Fractile in QlikView

FOR Each FieldValueList Fractile

mbespartochnyy — Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

I have been hitting a wall with a simple FOR Each loop. I'm running out of ideas what could be the issue so I'm turning to you in for help. I would like to get a Fractile values for a specific field aggregated by Country. The idea is that, some of the rebates may be outliers and I'd like to provide a user ability to exclude outliers if they choose to.

Attached is a sample app I made. In the script I:

- Load Unique Key, Country, and Rebate fields

- Find rebate fractiles for each country

- Store fractile values in variables

- Evaluate each rebate value to see if they are within our outside of frictile boundaries

- Add the results back to Stats table

For reason beyond me, the script as it is works marvelously for the first country, but fails for the rest of the countries. Does anyone know what I'm missing here? Is there a better way to do For Each loop in this scenario?

Many Thanks,

Mikhail Bespartochnyy

Re: FOR Each FieldValueList Fractile

mbespartochnyy — Tue, 08 Nov 2016 14:39:23 GMT

I found a solution to my own problem. I still have no idea as to what's going on with FieldValueList() function or why it's working only for the first value in the list, but I was able to use a simple Group By function to solve the problem. The group by is much simpler and more intuitive and does the same thing in this case. I figured I'd share it in case someone else is having the same issue. Attached is revised app containing original and final scripts.

P.S. If you can shed some light on where I went wrong with FieldValueList() function, please do, I'm still very curious why it didn't work.

Re: FOR Each FieldValueList Fractile

sunny_talwar — Tue, 08 Nov 2016 14:57:01 GMT

Hey Mikhail -

I believe you are running into the same issue which we might have discussed previously also. I think after your first left join the new fields (Outlier_25, Outlier_15, & Outlier_5) are now available in the Stats table and when you do the second join, the values of these won't match and hence won't be joined.

Instead of joining them within the loop, collect these values in a temp table

TempTable:

LOAD UniqueKey,

If(Rebate >= $(v25th) and Rebate <= $(v75th), 'Not Outlier', 'Outlier') as Outlier_25,

If(Rebate >= $(v15th) and Rebate <= $(v85th), 'Not Outlier', 'Outlier') as Outlier_15,

If(Rebate >= $(v5th) and Rebate <= $(v95th), 'Not Outlier', 'Outlier') as Outlier_5

Resident Stats

Where Country = '$(vCountry)';

and after the loop is over, do a left join

Left Join (Stats)

LOAD *

Resident TempTable;

DROP Table TempTable;

Attaching your sample back

Best,

Sunny

Re: FOR Each FieldValueList Fractile

sunny_talwar — Tue, 08 Nov 2016 14:58:26 GMT

Like I pointed out, I don't think the issue is with FieldValueList() function. The issue is the order of your execution of Join. See if the below script makes sense .

UPDATE: Although I think Group By might be a good way to go about this as well

Re: FOR Each FieldValueList Fractile

marcus_sommer — Tue, 08 Nov 2016 15:14:03 GMT

I agree with Sunny - there is no issue with the fieldvaluelist-function. If you looked on the script-log or the execution-window you will see that the loop runs through all fieldvalues - but there are some issues with the logic and the syntax, for example the not supported DISTINCT within the fractile-function.

- Marcus