Comparing unit prices from data tables - best way to do this?

Help
1

Hi all

I’m doing a practice project where I get the prices of items from three different supermarkets and have to use this data to produce an output spreadsheet.

On the spreadsheet, I’d like 3 tabs. 1 for each supermarket listing the product and the cheapest price e.g

(ASDA Tab)

ASDA Baked Beans 29p
Chicken Breast pieces £1.29
Apples 40p etc

(Morrisons Tab)

Morrisons Baked Beans 32p
Chicken Breast pieces £1.45
Apples 44p etc

To get to this stage, is it best to have one data table that has all the products in for each supermarket (e.g one data table for all Tesco products, one data table for ASDA products) or a separate data table for each product for each supermarket (e.g a data table for individual shopping items for Tesco e.g a data table for Tesco apples, a data table for Tesco chicken breast etc the repeat for other two supermarkets)?

Then to get the cheapest product from each supermarket, would that be a case of using the filter data table activity to filter out the data table to get the cheapest product for each item in the supermarket so all others are disregarded (e.g searching for baked beans returns around 50 items, I want the cheapest one)?

Finally, to paste the final shopping list items on to the spreadsheet, how will I get Ui Path to paste in to the ‘next empty cell’ e.g if columns A2 and B2 say ASDA Baked Beans 29p, how will Ui path know to paste Chicken Breast pieces £1.29 in to columns A3 and B3?

Sorry there are a number of questions here - quite a few important principals for me to learn!

Any help much appreciated,
Thanks
Jordan

2

You have to find the last row in the sheet and then use write cell to write the data into required cells.

Use Read Range of the sheet, output will be data table.
noOfRows = outputDataTable.Rows.Count

WriteCell Activity, address = "A" & (noOfRows+1).ToString , Value = "Chicken Breast pieces"

WriteCell Activity, address = "B" & (noOfRows+1).ToString , Value = "1.29"

3

I would suggest it would be good if you store the data by supermarket wise.

Reason : If you are storing Product wise and some day - a particular product is not available in one store , you have to store zero or null as its value.

Get Cheapest

  1. Create Datatable for ASDA with 3 columns : Name , price and ID

  2. Add items to it .
    Baked Beans , 29p , ID001
    Chicken Breast pieces , 29p , ID002.

  3. Create Datatable Morrisons with 3 columns : Name , price and ID

  4. Add items to it .
    Morrisons Baked Beans , 31p , ID001
    Chicken Breast pieces , 1.25p , ID002.

As you can see , For every particular type of item you have a uniqueID now (ID001 / ID002)

At the end You can loop for each row in one Datatable and compare it with other based on the unique id.

FurtherMore The Combination of UniqueID and product can be centralised via some excel or Database.

Interesting Topic :slight_smile:

Waiting for more suggestions.

4

@KarthikByggari this makes logical sense, thank you for the explanation. I will eventually build my process so that it could be a ‘weekly’ spreadsheet of items for each supermarket so I will need to add to the next empty line so I will return to this explanation, thank you :slight_smile:

5

Hi @mukeshkala, love the suggestion around adding an ID so I can then compare which supermarket has the cheapest of that particular product e.g ‘baked beans’. I will continue to build my xaml and let you know how I get on :slight_smile:
Thanks for replying,
Jordan