topic Re: Change Static variables and expression to Dynamic var and expr in QlikView

Change Static variables and expression to Dynamic var and expr

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

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

Re: Change Static variables and expression to Dynamic var and expr

a_mullick — Wed, 26 Apr 2017 15:16:33 GMT

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

Re: Change Static variables and expression to Dynamic var and expr

amber2000 — Wed, 26 Apr 2017 15:49:49 GMT

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

Re: Change Static variables and expression to Dynamic var and expr

a_mullick — Wed, 26 Apr 2017 16:14:17 GMT

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.

Re: Change Static variables and expression to Dynamic var and expr

amber2000 — Wed, 26 Apr 2017 16:44:56 GMT

Hi Azam,

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

Monique

Re: Change Static variables and expression to Dynamic var and expr

amber2000 — Tue, 02 May 2017 09:41:21 GMT

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

Kind regards,

Monique

Re: Change Static variables and expression to Dynamic var and expr

marcus_sommer — Tue, 02 May 2017 11:12:24 GMT

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 '>=*' condition. Is it possible?

- Marcus

Re: Change Static variables and expression to Dynamic var and expr

amber2000 — Wed, 03 May 2017 11:18:50 GMT

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

Re: Change Static variables and expression to Dynamic var and expr

marcus_sommer — Wed, 03 May 2017 11:54:40 GMT

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

Re: Change Static variables and expression to Dynamic var and expr

amber2000 — Wed, 03 May 2017 12:43:30 GMT

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

Re: Change Static variables and expression to Dynamic var and expr

marcus_sommer — Wed, 03 May 2017 13:18:10 GMT

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

Re: Change Static variables and expression to Dynamic var and expr

amber2000 — Wed, 03 May 2017 14:10:35 GMT

Marcus,

The table looks OK to me.

I'll attach the newest version.

Kind regards,

Monique

Re: Change Static variables and expression to Dynamic var and expr

marcus_sommer — Wed, 03 May 2017 14:42:56 GMT

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

Re: Change Static variables and expression to Dynamic var and expr

amber2000 — Wed, 03 May 2017 16:08:21 GMT

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

Re: Change Static variables and expression to Dynamic var and expr

marcus_sommer — Thu, 04 May 2017 06:32:46 GMT

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

Re: Change Static variables and expression to Dynamic var and expr

amber2000 — Thu, 04 May 2017 11:19:30 GMT

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

Re: Change Static variables and expression to Dynamic var and expr

marcus_sommer — Thu, 04 May 2017 11:31:12 GMT

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

Re: Change Static variables and expression to Dynamic var and expr

amber2000 — Thu, 04 May 2017 11:42:17 GMT

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) ?

Re: Change Static variables and expression to Dynamic var and expr

marcus_sommer — Thu, 04 May 2017 11:49:33 GMT

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

Re: Change Static variables and expression to Dynamic var and expr

amber2000 — Thu, 04 May 2017 13:04:19 GMT

Thank you so much