Solved: Change Static variables and expression to Dynamic ... - Page 2 - Qlik Community

amber2000 · ‎2017-04-26

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

marcus_sommer · ‎2017-05-03

Do your Staffel-table look ok? If yes, an adapted version of my pick(match()) suggestion should work. Maybe you provide a new version of your app.

- Marcus

amber2000 · ‎2017-05-03

Marcus,

The table looks OK to me.

I'll attach the newest version.

Kind regards,

Monique

marcus_sommer · ‎2017-05-03

In general it seems to work - the difference by the second dimensionvalue seems to be caused from a difference between the origin variable-values and the new inline-table. Please verify that they are identically.

- Marcus

amber2000 · ‎2017-05-03

Marcus,

The Original calculation isn't quite wright, there's a variable missing (ProdStaffel48) and that's why there is a difference.

Your solution to get rid of the variables is great and works fine.

Now I've added a set of fields to the excel table with different ProdFactorValues.

Depending on the Aisle Zone Description Zuivel gekoeld blok F the expression should use the new fields to calculate the Target, for all the other Aisle Zone Descriptions the expression should use the fields we've tested.

ProdStaffel	ProdStafValue	ProdFactor	ProdFactorValue	ProdStaffel_F	ProdStafValue_F	ProdFactor_F	ProdFactorValue_F
ProdStaffel1	1	ProdFactor1	7.9	ProdStaffel1	1	ProdFactor1	3.9
ProdStaffel2	2	ProdFactor2	7.8	ProdStaffel2	2	ProdFactor2	3.9
ProdStaffel3	3	ProdFactor3	7.7	ProdStaffel3	3	ProdFactor3	3.8
ProdStaffel4	4	ProdFactor4	7.6	ProdStaffel4	4	ProdFactor4	3.8
ProdStaffel5	5	ProdFactor5	7.5	ProdStaffel5	5	ProdFactor5	3.7

I'm trying to write a simple If statement with 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)))

,

//Else

,#Colli/#Assignments *

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

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

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

Can you please advise?

Kind regards

Monique

marcus_sommer · ‎2017-05-04

The reason for no results are the double-quotes around: "Zuivel gekoeld blok F" which qlikview will handle as a field and not as string - if you used single-quotes like this it will work: 'Zuivel gekoeld blok F'.

- Marcus

amber2000 · ‎2017-05-04

Thanks a million Marcus.

I'm over the moon with the result and it's a clean and manageable solution.

So glad that there are people who share their knowledge and time to help us.

Just one question remains, the syntax for Pick is Pick (position{, Expression})

Can you tell me how this relates for this statement please, I want to understand what's happening instead of just copying the expression and not knowing how it works.

#Colli/#Assignments *

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

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

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

Kind regards,

Monique

marcus_sommer · ‎2017-05-04

I think the following practical example made more clear what's happening:

pick(match(Month,

1, 2, 3, 4),

'Jan', 'Feb', 'Mar', 'Apr')

If Month = 2 it will return 'Feb' because the value 2 of Month will be found on the second position of the match-function which is also 2 in our case and 'Feb' is on the second position within the value-list of the pick-function. Used in this way it's a kind of a lookup.

- Marcus

amber2000 · ‎2017-05-04

So to check that I understand it am I correct to say that:

$(=concat(ProdStaffel, ',', ExcelRecNo)), is the first value to lookup (like in your ex.jan)

and $(=concat(ProdFactor, ',', ExcelRecNo)) is the second value to lookup (lik in your ex.feb) ?

marcus_sommer · ‎2017-05-04

Yes, exactly. The concat-functions within $-sign expansion are creating just these value-lists from the fields to save the efforts to write all of them manually. Here is a very good explanation to this feature: The Magic of Dollar Expansions.

- Marcus

amber2000 · ‎2017-05-04

Thank you so much

Change Static variables and expression to Dynamic var and expr