Transpose Grouped Values to an Report Datatable

Help Studio
, ,
#1

I have a input like this:

image

I need output like:

image

I created output DT using Build Data Table but when adding Add Data Row it add multiple times. Any alternativ approaches? Am New to UiPath tool, plz guide me on the same :pray:

@ppr @Lahiru.Fernando @NIVED_NAMBIAR @Pablito @Palaniyappan

Any help is appreciated!!!

#2

Hi @cybzom can u Share the sample excel file?

1 Like
#3

Hello @NIVED_NAMBIAR, plz find the file:

Input.xlsx (8.5 KB)

Any help would be appreciated!!! Thanks!!!

#4

@NIVED_NAMBIAR @ppr plz hav a look at the excel… kind regards, kiran sami.

1 Like
#5

@cybzom

give a try on following:

build the output datatable within the structure a from above:

Give a try on following as it is a quick Prototype:

(From d In dtdata.AsEnumerable
Group d By k=d(“Specifics”).toString.Trim Into grp=Group
Let lk = grp.ToDictionary(Of String, Object)(Function (x) x(0).toString.Trim, Function (x) x(2))
Let ra = {“A”,“B”,“C”,“D”,“E”}.Select(Function (v) lk(v)).toArray
Let ra2 = ra.Prepend(k).toArray
Select dtOutput.Rows.Add(ra2)).CopyToDataTable

Maybe you want to bring some dynamics and robustness into the code. Therefore you can just split the groups into a list(of list(Of DataRow)) (outer list=Group, Inner list=GroupMembers)

(From d In dtdata.AsEnumerable
Group d By k=d(“Specifics”).toString.Trim Into grp=Group
Select grp.toList).toList

And process the groups within a for each. Within a nested process block the group memebrs can be processed

2 Likes
#6

@ppr thanks for ur time…

I would like to update that this structure is dynamic so giving it liek {a,b,c,d,e} inside the expression won’t always work in my casee… thats the issue… i want to do it dyamic.

pls provude some response if possible… thanks again for ur time sir…

#7

sir if ur are free, can u plz provide a sample xaml file…?? it would be of great help!!! plz!! also i need the output i dt format… as shown in picc… @ppr

sorry for 2 many questions… am new to this field… but definitely willing to learn and do more hard work sir!!

#8

@ppr :slight_smile: help!! :pray: :pray:!!

#9

Hi @cybzom

Here is the workflow
sample workflow.zip (41.6 KB)

I think this is the output u required in final

image
image874Ă—322 9.12 KB

Mark it as solution if it resolves ur query

Regards
Nived N

Happy Automation

#10

@cybzom
find starter help handling it dynamic implemented on a reduced sample set:
Transpose_GroupBlock.xaml (16.0 KB)

we can see the dynamics on row t3 as this group fills up a column which only populated for this group
grafik

Setting up dynamicly the columns for the output table:
grafik

geting the groups into a list(of list(of Datarow) as mentioned above:
grafik

Group processing:

  • Iterate ofer the groups
  • create a temporary new empty row
  • add the key value from the group:
    grafik
  • filter for the values for the different locations, if not present use nothing as value
  • add datarow to output datatable

grafik
grafik703Ă—760 53.5 KB

1 Like
#11

thanks @NIVED_NAMBIAR nived… but I am getting this error:

Add Data Row: Input array is longer than the number of columns in this table on list 3… what is the issue?

#12

image

@cybzom
I am getting it correctly
sample workflow.zip (41.6 KB)

Please check the workflow

Regards
Nived N

#13

its like if there are duplications in city name and specifics right, its throwing this error but it wont during my run so i guess i’m gud :slight_smile:

let me run it some more times and then update u … appreciate ur quick response sir…

#14

Did u use different datatsets?

#15

no… same with 1000s or rows of data…

#16

Hi @cybzom

Did the issue got resolved ?

1 Like