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

[amber2000]

Hi Marcus,

Sorry to reopen this discussion again but I'm having trouble with the If statement, could you help me with this please?.

When I use one if statement then everything works fine.

ex.with 1 IF statement that works perfectly:

if([Aisle Zone Description] = 'Zuivel gekoeld blok F',

    #Colli/#Assignments *

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

    $(=concat(ProdStafValue_F, ',', RowNo))),   

    $(=concat(ProdFactorValue_F, ',', RowNo)))

, 

    #Colli/#Assignments *

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

    $(=concat(ProdStafValue, ',', RowNo))),   

    $(=concat(ProdFactorValue, ',', RowNo))))

---------------------------------------------------------------------------------------------------------------------------------------------------------------------

I've tested the if([Aisle Zone Description] = 'Zuurwaren/Mosselen'  like I did in the above example and that works perfectly:

if([Aisle Zone Description] = 'Zuurwaren/Mosselen',

    #Colli/#Assignments *

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

    $(=concat(ProdStafValue_C, ',', RowNo))),   

    $(=concat(ProdFactorValue_C, ',', RowNo)))

, 

    #Colli/#Assignments *

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

    $(=concat(ProdStafValue, ',', RowNo))),   

    $(=concat(ProdFactorValue, ',', RowNo))))

---------------------------------------------------------------------------------------------------------------------------------------------------------------------

But when I combine the 2 IF statements then I get no result:

if([Aisle Zone Description] = 'Zuivel gekoeld blok F'

, #Colli/#Assignments * pick(match(floor(#Colli/#Assignments), $(=concat(ProdStafValue_F, ',', RowNo))),$(=concat(ProdFactorValue_F, ',', RowNo)))

,

  if([Aisle Zone Description] = 'Zuurwaren/Mosselen'

  , #Colli/#Assignments * pick(match(floor(#Colli/#Assignments), $(=concat(ProdStafValue_C, ',', RowNo))), $(=concat(ProdFactorValue_C, ',', RowNo)))

  , #Colli/#Assignments * pick(match(floor(#Colli/#Assignments),  $(=concat(ProdStafValue, ',', RowNo))), $(=concat(ProdFactorValue, ',', RowNo)))

      )

  )

Kind regards,

Monique

[marcus_sommer]

I must admit that I didn't see an error or any reason why it should not work. But sometimes are syntax-issues or some logical problems very hard to detect. Often I try to simplify the task and then adding step by step more complexity. For example you could start with:

if([Aisle Zone Description] = 'Zuivel gekoeld blok F', 'A',

if([Aisle Zone Description] = 'Zuurwaren/Mosselen', 'B', 'C'))

and then you replaced the dummy values 'A/B/C' step by step with your expression-parts. I know this approach looked stupid but sometimes we develop a kind of blindness if we look to long on a certain challenge ...

- Marcus

[amber2000]

Thank you Marcus,

This is a great Tip, much easy'er to find out what's going wrong.

Kind regards,

Monique