How to make faster a lookup V

Help Studio
,
1

Hello,

I need help again.
I have two read range (2 excel) from which I have created 2 datatables.
Dt 1 and Dt2
Then I do a for each, inside a for each row, see the pic below @ppr help me out with this (thanks again)

image
image769Ă—519 15.2 KB

The thing is that it is toooo long when I launch the process. It can take 30min and I have to do this with two other excel.

The thing is that I have Three Excels
2 Excels with data that i need to take and put on the third one

The third excel has two extra columns empty and each empty column has to be filled but the two other excels… Hope I am clear …
The workflow do the job, but like I said it is very too long.

Can anyone has a faster way to do so ?
Or maybe a package for this activity lookup

thank you very much for your help !

@Silviu maybe you can help with this issue
@Parth_Doshi
Regards

2

Hi @najoua.abbaci

Did you tried with lookup datatable activity?

3

@NIVED_NAMBIAR thank you for answering me.
I tryied to use this activity but I got stuck, I didn’t know how to define the lookupValue.
Maybe you can help ?

4

check if this can help @najoua.abbaci Adding Specific Columns From One Data Table To Other - YouTube

1 Like
5

Can u share the scenario and sample excel file for reference?

6

Here is the workflow that I use

image
image663Ă—714 24.8 KB

And this is the sample excel files
Excel named in_FichierCSKS

image
image504Ă—634 30.5 KB

Excel named in_ExportMBEW

image
image1122Ă—651 23.9 KB

And the third one is the same that in_ExportMBEW, plus a column i have added named “societe” to got the societe code frome the first Excel

(hope it is clear)
Thnak you

7

Thank @Parth_Doshi for sharing.
I checked your video and I tryied to do the same. Some how it is working, but for some data, the result is 0, whereas there is a match, i dont know how come ?

thank you

8

check if the hidden or something like that because if it is working for most of the data then it should work.

9

I don’t know, I don’t see anything hidden or something similar.

What it is weird, it that when I uncheck the case “Preserve format” from one of the 2 Excel, then I have an error message regarding the Last Assign activity saying “no row at the position -1”
Exception Type : System.IndexOutOfRangeException.

Maybe this is why (even though I don’t really understang, I’m a rookie)

Also, I noticed that from my excel file (in your example it will be the Details2) there are similar values,
for instance, i’m looking for 0151 (id from details1 in your example) but in the details 2 there are 0151 and A0151, can be the issue ?

@Parth_Doshi

10

All i see in your project is meant to be super slow…

  1. You are using read range from system activities, dont you have excel?
  2. You have nested for each row activities and a linq query inside, maybe rethink all this.
1 Like
11

Oh, I just figure it out, why i have blank space. it is because it didn’t write if the id was already filled a first time.
a scrrenshot to clearify my saying :

image
image1438Ă—608 30.5 KB

Can you help me out with this @Parth_Doshi ? it does the same for the 2 columns that I have added and done a Lookup Data Table (“Société” and “EAN”)

12

Yes, I have changed and re-think my process thanks to @Parth_Doshi
I still use read range activity from system activities. But I do have Excel (I didn’t know that it can be slower).
and now i’m using a Lookup DataTable activity.
It is working but it doesn’t fill all the rows because there are duplicates in my Excel files but I need to keep this duplicates lines coz the “Article” is the same but not the “Site”. So I have to keep all the data, included duplicates.

13

you can have one more if condition after lookup
if(row already has value)
perform then steps
perform else steps
I hope this helps @najoua.abbaci

1 Like
14

Thank you for your answer !
Can you give me more details please @Parth_Doshi

15

sure before that you are using lookup data table right

if the value is found you are writing some data in other cell?
and what are you doing if value not found?

now after this when is the value coming empty for you?
because even if the value already exists it should over write it

16

Thank you for your precisions @Parth_Doshi
But, i’m sorry, i’m quite new in development and I try to do an If activity, but I can make it !

17

@Dave
@Manjuts90
I saw a post where you explain how to write a Vlookup function, can you please help me to do so in the cas that I exposed at the begining of my topic, please ?

To recap, I have two Excel file
Excel 1

image
image1148Ă—338 12.5 KB

Excel 2
image

I have to write into the Excel 1, Column “Société”, the value from Excel2 “Société”
The similar Key between the 2 Excel is
“Domaine Val…” (col B) = Excel 1
“Centre de coûts” (col A) = Excel 2

Can you help me to make a Vlookup function to have a faster workflow ?

Thank you in advance

18

@Parth_Doshi can you please help me out with this If activity that I have to add to fill the empty field ?
I tryied but it doesn’t work and I think that your solution is the most robust, instead of a Vlookup in Excel.
Thank you

19

Hey @najoua.abbaci
In lookup data table there is a property rowindex create a variable(int32) there and use in if condition if the value is found it will output 1 or else -1
so now your the condition is if(rowindex ==1) then use the assign statement as shown in the video or else it will go the next row

1 Like
20

Thank you for answering me so fast @Parth_Doshi

Following your video, I have already created 2 rowindex (one for each “LookUp activity”). So, which one do I have to use ? or do i have to create a third one ?

And where do I have to add the “if activity”, after the last lookup activity ?
And I tryied rowindex ==1, but i had an alert message. So I wrote rowindex =1 (is it ok?)
Thankx again !

UPDATE : it is ok i finally succeded ! I have changed a bit from your video. I used only one LookUpDatatable and I define directly into this activity all the values I needed.
So now it is working fine.

Thank you for your time and help !

1 Like