topic Set Expression to Solve a Specific Issue in QlikView

Set Expression to Solve a Specific Issue

Giovane — Mon, 12 Apr 2021 15:09:52 GMT

Hello all...

Please, consider bellow spreadsheet. In the last column I need the result of the accumulated <Parcial Mandioca (Kg)> column. As doing so, the number sequence, in the last column, must be as you can see in the showed spreadsheet. As you can see in the last line, yet, Year field (Ano) must be considered, and the accumulation must be done based on the Partial (Parcial) field.

Can you show me a Set Expression to perform the job?

Kind regards.

Ano	Parcial	De	Ate	Parcial Mandioca (Kg)	Média Parcial Amido	Produção Fécula Parcial (Kg)	Produção Fécula Parcial (Sc)	Parcial (Sc/T)	Peso Saco Fécula Úmida (kg/SC)	Parcial (g/SC)	Tx de Conversão Parcial (%)	Total Mandioca Acumulado (Kg)
2021	1	25/03/2021	25/03/2021	35.672,00	638,58	14.570,00	182,13	-	80,00	125,08	25,53%	35.672,00
2021	2	26/03/2021	27/03/2021	39.121,60	643,59	18.180,00	227,25	-	80,00	110,80	29,04%	74.793,60
2021	3	29/03/2021	29/03/2021	18.649,40	676,00	6.240,00	78,00	-	80,00	161,63	20,91%	93.443,00
2021	4	31/03/2021	01/04/2021	86.504,60	588,35	31.850,00	398,13	-	80,00	127,84	23,01%	179.947,60
2021	5	06/04/2021	06/04/2021	31.850,00	630,09	15.120,00	189,00	-	80,00	106,18	29,67%	211.797,60
2021	6	06/04/2021	07/04/2021	34.398,00	616,44	16.090,00	201,13	-	80,00	105,43	29,23%	246.195,60
2021	7	07/04/2021	08/04/2021	48.725,60	621,41	22.720,00	284,00	-	80,00	106,61	29,14%	294.921,20
2021	8	08/04/2021	09/04/2021	46.765,60	624,66	23.010,00	287,63	-	80,00	101,56	30,75%	341.686,80
2021	9	09/04/2021	09/04/2021	16.640,40	570,00	7.700,00	96,25	-	80,00	98,55	28,92%	358.327,20
2020	1	08/05/2020	18/05/2020	164.444,60	650,72	58.790,00	734,88	-	80,00	145,61	22,34%	164.444,60

Re: Set Expression to Solve a Specific Issue

Dalton_Ruer — Tue, 13 Apr 2021 13:07:02 GMT

This really isn't a SET ANALYSIS problem. This can be handled easily in the LOAD SCRIPT using 2 awesome Qlik script function PREVIOUS and PEEK.

The PREVIOUS function does what it's name implies, it hands you the previous field value for the data you just read. Assume we have this is as our load script:

Data:
Load * Inline [
Year, Month, PartialValue
2020, 1, 100
2020, 2, 200
2020, 3, 300
2021, 1, 50
2021, 2, 100
2021, 3, 150
];

If we wanted to then create a new value called PreviousYearValue we could do something like this, note that I purposely sort the data in the order I want.

Join
Load
Year,
Month,
Previous(Year) as PreviousYearValue
resident Data
Order by Year, Month

That code would then yield the following table.

Notice that in the first result row we don't have a PreviousYearValue, because it's the first row. It's important to understand that because we need to handle that initial case. when we try to accumulate your values. Logically what we really want to do is say "If the Year you just read in is the same as the previous Year value then add my current PartialValue to the AccumulatedValue field, otherwise start my AccumulatedValue at the value of my PartialValue field."

If (Year = Previous(Year) , Previous(CummulativeValue) + PartialValue, PartialValue) as CummulativeValue

Notice I said logically because we have a problem. While we READ in the Previous YEAR value, we never READ in the CummulativeValue it is IN MEMORY only it wasn't read. So while using Previous(Year) is fine for our IF condition, Previous(CummulativeValue) will yield an error message for us:

That's where the PEEK function comes in handy. It let's me "peek" into my In Memory data, and not just the actual data that was read.

Join
Load
Year,
Month,
If (Year = Previous(Year) , Peek(CummulativeValue) + PartialValue, PartialValue) as CummulativeValue
resident Data
Order by Year, Month

Now we have what we need.

Re: Set Expression to Solve a Specific Issue

Giovane — Wed, 14 Apr 2021 13:53:12 GMT

Hello, Dalton.

Thank you very much for your carful response. Meticulously explained. You are the guy!!!

See you.