Change Static variables and expression to Dynamic var and expr

[amber2000]

Hello Qlikview Wizards, here is a brainteaser

I have inherited a qvw from a colleague and I want to change some of the logic but it's to complicated.

Therefore I want to ask your help.

The purpose of the pivot table in the  report is the calculation of productivity's relative to the target.

To calculate the Target there are variables used:

ProdStaffel and ProdFactor.

Example:

ProdStaffel1  (value = 1) corresponds with ProdFactor1 (value = 7,9)

ProdStaffel2  (value = 2) corresponds with ProdFactor2 (value = 7,8)

ProdStaffel3  (value = 3) corresponds with ProdFactor2 (value = 7,7)

.........

ProdStaffel99  (value = 99) corresponds with ProdFactor99 (value = 2,2)

ProdStaffel100 (value = 100) corresponds with ProdFactor100 (value = 2)

The expression is formed through nested if statements (around 99)

if (#Colli/#Assignments >= ProdStaffel1  and #Colli/#Assignments <= ProdStaffel2, #Colli/#Assignments  *ProdFactor1,

     if (#Colli/#Assignments >= ProdStaffel2  and #Colli/#Assignments <= ProdStaffel3, #Colli/#Assignments *ProdFactor2,

            if (#Colli/#Assignments >= ProdStaffel3  and #Colli/#Assignments <= ProdStaffel4, #Colli/#Assignments *ProdFactor3,

                 if (#Colli/#Assignments >= ProdStaffel4  and #Colli/#Assignments <= ProdStaffel5, #Colli/#Assignments *ProdFactor4,

...........

))))

So the Target calculation for each zone is the same.

This is correct for most of the zones but for a few zones (for example 'Zuivel gekoeld blok F') the values of variable ProdFactor should be different.

In the expression I need to be able to calculate the target depending on the Aisle Zone Description and the set of ProdFactor

Aisle Zone Description	#Colli	#Time Worked	#Assignments	Average Assignments	Productivity	Target	Percentage
Flowthru	78022	488:59:17	2114	36,91	159,56	166,08	96,07%
GRF 4de gamma	12911	66:14:21	238	54,25	194,91	206,14	94,55%
GRF gekoeld	16663	87:11:33	392	42,51	191,11	187,03	102,18%
PAT blok D	189	6:42:00	38	4,97	28,21	37,8	74,63%
Piece_Pick	1353	2:52:18	20	67,65	471,15	223,25	211,05%
Piece_Pick_BF	3988	8:26:28	94	42,43	472,45	186,67	253,09%
Zuivel gekoeld blok F	7336	62:50:05	162	45,28	116,75	194,72	59,96%
Zuivel ongekoeld	7404	59:04:11	267	27,73	125,34	146,97	85,28%
Zuivel Traiteur gekoeld blok H	3187	18:53:41	101	31,55	168,67	154,62	109,09%
Zuurwaren/Mosselen	2386	26:01:31	178	13,4	91,68	89,81	102,08%

This are the results now and as Example I take Aisle Zone Description: 'Flowthru' and the expression is

if (#Colli/#Assignments >= ProdStaffel36 and #Colli/#Assignments <= ProdStaffel37,#Colli/#Assignments *ProdFactor36,

#Colli (78022) / #Assignments (2114) = 36,91

36,91 * ProdFactor36 (value is 4,5)

For Aisle Zone Description: 'Zuivel gekoeld blok F' I need to set a different set of values for variable ProdFactor.

In this case the result of the expression would be

(#Colli / #Assignments = 45,28)  * (ProdFactor45 = 4,3)  = 194,70 

For this Aisle Zone Description 'Zuivel gekoeld blok F' the variables ProdFactor should have other values:

(#Colli / #Assignments = 45,28)  * (ProdFactor45 = 2,5)  = 113,20

Add a 100 new variables and add another clause to the if statement isn't a clean solution.

Can anyone please help me to get a clean, short and manageable solution for this problem?

I've attached the qvw where to see the solution as is at the moment.

Kind regards,

Monique

[a_mullick]

Hi Monique,

I've got a work in progress idea, maybe someone else can finish it...

It's based on the observation that the tests on '#Colli/#Assignments', which are in step s of 1. So it is possible to generate the corresponding ProdFactor, by concatenating ProdFactor with Floor(#Colli/#Assignment) to generate the appropriate factor value. Therefore, the expression is simplified, and you can now test for different zones, without getting lost in a sea of ')'s. But... and this is where someone else can help, I can't quite resolve the dynamically generated ProdFactor. I can't see why... anyway attached is an edited version with 3 new columns: 'ProdFactor Num' , 'ProdFactor Var Name, and 'ProdFactor Value'.

Thanks,

Azam

[amber2000]

Hi Azam,

Thank you for your response

I'm sorry but the result of expression ProdFactor value isn't correct.

ProdFactor Num 44 has in your example a value of 4,5 but if you look to the variable overview the value should be 4,3.

I'm not sure what you are trying to do.

Kind regards,

Monique

[a_mullick]

Hi,

Sorry for not being clearer. That is the problem I'm hoping someone else maybe able to help with - why all the values for ProdFactor come out at 4,50. If it did work (big if) then your expression for 'Target' would become:

// expression for #Colli/#Assignment

sum({$<[Jobcode Flag] = {"Productive"}>}[Assignment Total Colli]) /

count({$<[Jobcode Flag]= {"Productive"}>} DISTINCT [Assignment ID]) *

// expression for dynamically generated ProdFactorxx

$(='ProdFactor' &

Num(Floor(

sum({$<[Jobcode Flag] = {"Productive"}>}[Assignment Total Colli]) /

count({$<[Jobcode Flag]= {"Productive"}>} DISTINCT [Assignment ID])), '#.##0')

)

Thanks,

Azam

Additional comment 30/04/2017:

I believe that the dollar expansion is made first, before the expression is evaluated, so it cannot expand to different dimensions. In effect it is evaluating all values of [Assignment Total Colli] and all values of [Assignment ID] (like if it was an expression in a text object), which is why it returns the same value for every cell.

[amber2000]

Hi Azam,

Ok thanks I understand now what you're trying to do.

Monique

[amber2000]

Is there anybody who can help me with this problem please?

Kind regards,

Monique

[marcus_sommer]

I suggest to use a pick(match()) lookup instead of the nested if-loops whereby I would maintain all of the checking/matching prod-data within a small excel-table and not within variables. Such an excel could be created within a few minutes and is a lot easier to maintain as all those variables.

Your expression for Target could be look like this:

#Colli/#Assignments *

pick(match(floor(#Colli/#Assignments, 0.1),

     $(=concat(ProdStaffel, ',', ExcelRecNo)),

     $(=concat(ProdFactor, ',', ExcelRecNo)))

The concat() within the $-sign expansion served the purpose to assign all values of a certain field from your excel-maintaining table into a string which is then used as lookup- respectively return-value as if you had written those parameter-values manually (and because they are just numbers it's quite easy without the need of any quotes).

Another important part is floor-function because pick(match()) couldn't directly compare < or > it's rounding consequently up to one digit - which meant that your excel must contain this granularity (in your case maybe 500 - 600 values).

Here you will see some similar cases:

Re: Substitute to nested ifs

Re: Pick() Match() with '&gt;=*' condition. Is it possible?

- Marcus

[amber2000]

Hello Marcus,

I've been studying the examples you suggested but it's a little bit chinese for me.

I'm not used to use such complex structures (still a newbe in that area).

Also on myQlikview Server is no Excel apllication installed and our IT department wont ever allow it.

So I'm a little stuck now with the suggested solution.

Would it be the similar if I use an inline table with an extra field RowNo ?

In my example you can see the inline table and my attempt to use your expression.

The expression doesn't give me any values so there must be something I'm doing wrong.

Is it possible you can take a peek at my example please?

I will attach a new example.

Kind regards,

Monique

[marcus_sommer]

Yes, you could use such an inline-table with the following slight adjustments:

Staffel:

Load *, RowNo() as RowNo INLINE [

ProdStaffel,    ProdStafValue,    ProdFactor,         ProdFactorValue

ProdStaffel1    ,    1        ,    ProdFactor1        ,    "7,9"

ProdStaffel2    ,    2        ,    ProdFactor2        ,    "7,8"

ProdStaffel3    ,    3        ,    ProdFactor3        ,    "7.7"

....

] where len(ProdStaffel) > 5; // to exclude the ....

The default-delimiter is the comma so you will need double-quotes around field-values which contain commas - whereby I'm not sure if your system will interpret the comma as decimal-delimiter correctly or if it would be better to change it to a point - of course you could it also convert with something like:

num(num#(ProdFactorValue, '0,0', '.', ','))

Like above mentioned it's a lot more handy to use excel for it even if you hadn't it available on the server - you could create it on your local machine and then copy the data into the inline-table by adjusting the fileformat to:

] (txt, delimiter is \t) where len(ProdStaffel) > 5;

which will react to the tab-sign between the excel-columns.

- Marcus

[amber2000]

Hi Marcus,

Id did as you suggested and used my local excel.

How can I now calculate the Target based on the excel values instead of the variables?

Kind regards,

Monique