HI, i want to extract the max H’s and max W’s percentages for every ‘IdPrueba’ that exist but dont know how…
In the column ‘NomJson’ is the correlation between the H/W and the %
Example: 507 MaxH= H1 100% | MaxW= W1 95%
Hello @itorre Try this approach
First use Read Range to read the Excel into a DataTable dt_Data then create a result DataTable using Build DataTable with columns as IdPrueba (String), MaxH (String) and MaxW (String) and name it dt_Result then get all unique IdPrueba using Assign activity
uniqueIds = dtData.AsEnumerable().Select(Function(r) r("IdPrueba").ToString()).Distinct().ToList()
Then use For Each currentItem in uniqueIds then use Assign with maxH = 0, maxW = 0 then again For Each Row in dt_Data then use if condition as row("IdPrueba").ToString = item then again if condition with row("NomJson").ToString.Contains("H") and check if CInt(row("Valor")) > maxH, then assign maxH = CInt(row("Valor")) then agaiin use if row("NomJson").ToString.Contains("W") the again check if CInt(row("Valor")) > maxW, then assign maxW = CInt(row("Valor"))
then after loop use Add Data Row in dt_Result with {item, "H1 " & maxH & "%", "W1 " & maxW & "%"} then use Write Range to export dt_Result back to Excel
Cheers
hi, @itorre You can solve it by grouping the data by IdPrueba and then picking the max values separately for H and W. For each IdPrueba: Take the rows where NomJson contains H → select the one with the highest % (Valor).
Take the rows where NomJson contains W → select the one with the highest %.
This way you’ll end up with a result table like: IdPrueba | MaxH | MaxW
Example: for 507 → MaxH = H1 100% | MaxW = W1 95%.
Hello @itorre
Use this query
From row In dt.AsEnumerable()
Group row By Id = row.Field(Of String)(“IdPrueba”) Into grp = Group
Select New With {
.IdPrueba = Id,
.MaxH = grp.Where(Function(r) r.Field(Of String)(“NomJson”).ToUpper().Contains(“TIPOEXT”)) _
.Where(Function(r) r.Field(Of String)(“Id de campo”).ToUpper().Contains(“H”)) _
.Select(Function(r) Convert.ToInt32(r.Field(Of String)(“Valor”))) _
.DefaultIfEmpty(0).Max(),
.MaxW = grp.Where(Function(r) r.Field(Of String)(“NomJson”).ToUpper().Contains(“TIPOEXT”)) _
.Where(Function(r) r.Field(Of String)(“Id de campo”).ToUpper().Contains(“W”)) _
.Select(Function(r) Convert.ToInt32(r.Field(Of String)(“Valor”))) _
.DefaultIfEmpty(0).Max()
}
It will give you output
IdPrueba | MaxH | MaxW
502 | 100 | 90
507 | 100 | 95
501 | 80 | 70
Reagrds,
Rajesh Rane
Hello @itorre,
To find the maximum H and W percentages for each ‘IdPrueba’, you need to follow a few simple steps. First, identify the measurement type by looking at the last two characters of the ‘Clase Id’ column. Values like ‘H1’ or ‘H2’ indicate an ‘H’ measurement, while ‘W1’ or ‘W2’ indicate a ‘W’ measurement. Next, extract the percentage value from the ‘Nombre del campo’ column, which is the number listed right after “SCOPE (%)”. After this, you should group the data by ‘IdPrueba’. Within each group, filter for all ‘H’ measurements and identify the row with the highest percentage to find the Max H %. Do the same for all ‘W’ measurements to find the Max W %. For instance, in ‘IdPrueba’ 507, the ‘H’ percentages are 1% and 2%. The maximum is 2%, which is associated with ‘H2’ in the ‘Clase Id’ column, making the result Max H = H2 2%. The ‘W’ percentage is 1%, which is associated with ‘W1’, so the result is Max W = W1 1%. This process allows you to efficiently find the highest percentage values for both H and W for every unique ‘IdPrueba’ in your data.
But this wouldnt be empty?
How do i use a query like that in UiPath?
Use assign activity
save it to datatable variable and in value paste this query
dt_Data will be the datatable of your read range and dt_Result will be the build Datatable you created with the column schema.
Do i have to build the datatable? or it creates by itsself?
You have to build it using Build Datatable Activity
Sorry but i think its wrong, you cant find H’s or W’s in “NomJson”, you have to find it in “Valor” and the correlate it with his percentage by ALCANCEEXT1->TIPOEXT1, ALCANCEEXT2->TIPOEXT2
- I also need to know which H and W is, H2-90% | W1 100% for example
Okay got it so you have to use Build DataTable dtPairs with columns as
IdPrueba(String)Pair(String)Tipo(String) — default""Porc(Int32) — default0
then loop through dt_Data row by row and if NomJson starts with TIPOEXT create/find a row in dtPairs for that IdPrueba + pair number and store the H/W label in Tipo and if NomJson starts with ALCANCEEXT, create/find the same row and store the percentage in Porc and convert string to number, remove % and if row doesn’t exist yet the Add new row or if exists then update existing row.
Where would you add this code in the first answer u gave me?
I think this will remove the error so first use Read Range and dt_Data then Build DataTable and dt_Result with columns IdPrueba, MaxH, MaxW
and again Build DataTable dtPairs with columns IdPrueba, Pair, Tipo=“”, Porc=0 then
For Each Row in dt_Data and fill dtPairs
If TIPOEXT then set Tipo
and If ALCANCEEXT then set Porc
then Assign uniqueIds from dt_Data
then For Each currentId in uniqueIds and initialize maxH=0, maxW=0, HLabel=“”, WLabel=“”
then again use For Each Row in dtPairs filtered by currentId and update max H/W and labels
then Add Data Row to dt_Result with {currentId, HLabel & " " & maxH & "%", WLabel & " " & maxW & "%"}
then in Write Range put dt_Result
i dont think im understanding you, would you mind to link a image or a workflow so it could help me understand it