I'm new to Dataprep and I'm struggling to write basic formulas and conditions.
In the image below you can see what I'm trying to do. DataPrep screen
I have 'PROJETO' Column, 'PEP' Column and 'QUANTIDADE' Column. I want to sum 'QUANTIDADE' values that match conditions in PEP column.
Example:
| PROJETO | PEP | QUANTIDADE |
| -------- | -------------- |--------|
| E.ADBR001 |E.ADBR001.L2.EN.IS.XPTO|1|
| E.ADBR001 |E.ADBR001.L2.EN.IS.XPTO|2|
| E.ADBR001 |E.ADBR001.L2.EN.IS.XPTO|3|
| E.ADBR008 |E.ADBR001.L2.EN.IS.XPTO|4|
| E.ADBR008 |E.ADBR001.L2.EN.IS.XPTO|5|
| E.ADBR008 |E.ADBR001.L2.EN.IS.XPTO|6|
| E.ADBR001 |E.ADBR001.L2.EN.AT.XPTO|10|
| E.ADBR001 |E.ADBR001.L2.EN.AT.XPTO|20|
| E.ADBR001 |E.ADBR001.L2.EN.AT.XPTO|30|
| E.ADBR008 |E.ADBR001.L2.EN.AT.XPTO|40|
| E.ADBR008 |E.ADBR001.L2.EN.AT.XPTO|50|
| E.ADBR008 |E.ADBR001.L2.EN.AT.XPTO|60|
In the case of
`PEP LIKE {PROJETO}{any character}EN.IS{any character}`
i would like to sum quantidade column and group in a PROJETO row (whatever in a new table or column), like this:
| PROJETO | EN.IS | EN.AT |
|---|---|---|
| E.ADBR001 | 6 (3+2+1) | 60 (30+20+10) |
| E.ADBR008 | 15 (6+5+4) | 150 (60+50+40) |
I tried something like
`SUMIF(QUANTIDADE, PEP='%*EN.IS%*')`
in "Values" in "Group By" action from Dataprep. I really dont know if the use of %*, as I read on documentation, indicate any character in a string, and I'm really struggling to manipulate strings.
Can someone help me?