【Query Builder：Expression】ValueStoreSet and ValueStoreGet

# Introduction #
I am going to see how **ValueStoreSet** and **ValueStoreGet** expressions work in this post.
With these expressions, we can store a value into memory and retrieve the value from memory to use in a query session.
It is similar to the concept of preparing a variable in a general programming language, assigning a value to it, and then extracting and using the value.

# Preparing Resources #
When writing this article, I couldn't think of a good practical example. 
Therefore, although it's not practical, I have prepared an example using these expressions to display the following results.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a0f9c435-c00e-0fe4-7c80-9dafa851a8db.png)

I will use a UNION query.
In the first query of the UNION (Parent Query), I will calculate the total amount of items in yellow, blue, and red from this dataset, and store them in memory using **ValueStoreSet** expression.
The results will then be displayed using **ValueStoreGet** expression in the second and subsequent queries.

The dataset is;
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1001a07b-dd04-4264-abfe-6d2218b53f51.png)

These rows are by UNION queries;
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7e183e7d-1a2b-043f-4dd7-9c696a0a1923.png)

### Query Definition ###
#### The parent query ####
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/83ff2030-d9a8-10ec-4021-94d741715fc5.png)
```
"Code::String","Key Colour::String","Amount::Number"
"A100","RED",200
"A200","BLUE",159
"A300","BLUE",356
"A400","RED",211
"A500","YELLOW",255
"A600","RED",1200
"A700","BLUE",2100
"A800","RED",1940
"A800","BLUE",100
"A900","RED",320
"A100","RED",89
"A110","BLUE",20
"A120","YELLOW",12569
"A130","RED",568
"A140","RED",400
"A150","YELLOW",550
"A160","RED",600
"A170","BLUE",570
"A180","YELLOW",420
```
1. A comma separated list shown above is used as the data source
2. These 3 expressions actually calculate the total amount for each colour
    In case of `YELLOW`, when it is the first row and the row says "YELLOW" shows its Amount, otherwise 0
    From the second row, if the current row says "YELLOW", add its amount to the result of previous row of this column, otherwise just returns what the previous row of this column has

    When it comes to the last row, this column will have the total amount of the target colour
    
    ```
    IIF(StartOfReport
        ,IIF({%KeyCol}="YELLOW"
        	, {%Amount}
        	,0
    	)
        ,IIF({%KeyCol}="YELLOW"
        	,{%Previous:TotalforYELLOW}+{%Amount}
        	,{%Previous:TotalforYELLOW}
    	)
    )
    ```
3. They store the calculated total amount for each colour into memory with **ValueStoreSet** expression
    In case of **YELLOW**, this expression column does storing the current value of the above expression column for YELLOW by using the expression
    When it comes to the last row, the total amount for each colour is in memory with the given code `YELLOWTOTAL`. 
    ```
    ValueStoreSet("YELLOWTOTAL", {%TotalforYELLOW})
    ```
#### The UNION queries ####
There are 3 unions defined.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6c3545ed-5c81-7c53-a538-a56f0d237e20.png)
Each union query displays the total amount for each colour stored in memory.
These queries are designed to return only one row as it just needs to display the total.
This is the example of "YELLOW".
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/77de2d78-886d-957b-ed64-33b1c11ea469.png)
The expressions (Code) and (Key Colour) show these titles.
 ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ed759cee-7115-8a5b-a79d-50c30f864053.png)

# The Result #
As the result, the total amounts were well calculated and stored in memory by the parent query, and the total amounts were retrieved from memory by the union queries well.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/aea6445e-b1d4-bed5-430e-bceb60bdb118.png)

# Afterword #
The solution mentioned here doesn't have a particular meaning, but I hope that this can show you how these expressions store and retrieve value in/out from memory.