Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Giovane
Creator
Creator

Set Expression to Solve a Specific Issue

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.

 

AnoParcialDeAteParcial Mandioca (Kg)Média Parcial AmidoProduçã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)
2021125/03/202125/03/202135.672,00638,5814.570,00182,13-80,00125,0825,53%35.672,00
2021226/03/202127/03/202139.121,60643,5918.180,00227,25-80,00110,8029,04%74.793,60
2021329/03/202129/03/202118.649,40676,006.240,0078,00-80,00161,6320,91%93.443,00
2021431/03/202101/04/202186.504,60588,3531.850,00398,13-80,00127,8423,01%179.947,60
2021506/04/202106/04/202131.850,00630,0915.120,00189,00-80,00106,1829,67%211.797,60
2021606/04/202107/04/202134.398,00616,4416.090,00201,13-80,00105,4329,23%246.195,60
2021707/04/202108/04/202148.725,60621,4122.720,00284,00-80,00106,6129,14%294.921,20
2021808/04/202109/04/202146.765,60624,6623.010,00287,63-80,00101,5630,75%341.686,80
2021909/04/202109/04/202116.640,40570,007.700,0096,25-80,0098,5528,92%358.327,20
2020108/05/202018/05/2020164.444,60650,7258.790,00734,88-80,00145,6122,34%164.444,60
 

 

1 Solution

Accepted Solutions
Dalton_Ruer
Support
Support

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. 

PreviousYearValue.png

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:

PreviousError.png

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. 

PreviousPeek.png

 

View solution in original post

2 Replies
Dalton_Ruer
Support
Support

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. 

PreviousYearValue.png

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:

PreviousError.png

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. 

PreviousPeek.png

 

Giovane
Creator
Creator
Author

Hello, Dalton.

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

See you.