Select row from data table which satisfies "contains" condition of a variable in specified order too

Help
, , , ,
1

Please help me with this -
I have data table having structure like this -
Screenshot (153)
And a variable like this - Route=Jhabua/Vadodara/Barmer.
Now I want to pick that route from data table which contains all districts within variable in this order only. If two or more route matches this condition then route with higher frequency should get selected.

Thanks in advance…

2

Hi Can you share the excel sheet?

Thanks! @SIIL_Tracking

3

Sure…PFA

Thanks for supportRoute_M.xlsx (9.2 KB)

4

selectRoute.xaml (7.4 KB)

Please have a look…!

Thanks!

5

Actually the route variable contains the district of customers to be shipped. And in data table the whole route is maintained. So it is not always possible that the districts will appear one after another.
Eg -
Route= A/D/G
Master -

  1. A/B/G/D/E
  2. A/C/D/E/G
    3.A/B/C/D/E

So result will be the second route.

6

So you want Jhabua/Vadodara/Barmer this three cities should be included in the route and among all the records it should return the row with maximum frequency.
Correct ?

Thanks
@SIIL_Tracking

7

You can do like this
Read Range-----DT1
Filter Datatable
Row Field
“Route” Contains “Jhabua”
AND
“Route” Contains “Vadodara”
AND
“Route” Contains “Barmer”

SAVE it in variable say DT2

it will give all rows those contains this dist.

8

Yes…but in this order only and it is possible that district may not appear one after another in route.

9

Hi @SIIL_Tracking,

selectRoute.xaml (8.5 KB)

Updated as per your requirement, please test it.

Thanks!

10

Workflow Attached According to your requirement
Main.xaml (7.1 KB)

Thanks
@SIIL_Tracking

11

Your solution works fine…but it is not considering the sequence of appearance of districts. It is selecting same route for this two combination -
Case 1) Route=A/B/F
Result=A/B/C/D/E/F
Case 2) Route=B/A/F
Result=A/B/C/D/E/F
In Case 2 the result should be that route in which B is appearing before A, like B/C/A/D/F or similar route.

Thanks in advance @kadiravan_kalidoss

12

Hi @SIIL_Tracking,

My logic will check whether all the districts are present in that route or not,
if all the input districts are present,
then based on the frequency it will select the record which is having the max frequency…!

Please provide exact input data and expected output from the sheet.!

Thanks…!

13

Please find attach Route_Master fileRoute_M.xlsx (9.1 KB)

Case 1)
Input Route=Jhabua/Ahmedabad/Barmer Result=Nagpur/Chindwara/Betul/Harda/Sehore/Dewas/Indore/Dhar/JHABUA/DAHOD/Vadodara/Godhra/Kheda/Ahmedabad/Gandhinagar/Mehsana/Patan/Banaskantha/JALORE/BARMER

Case 2)
Input Route=Ahmedabad/Jhabua/Barmer
Result=Nagpur/CHINDAWARA/Betul/Harda/Sehore/Dewas/Indore/Dhar/DAHOD/Vadodara/Godhra/Ahmedabad/Mehsana/JHABUA/Sabarkantha/Patan/Banaskantha/JALORE/BARMER

Thanks in advance @kadiravan_kalidoss

14

Hello,
Please find attached file and check , i had used input dialog for assign Input Route

Sequence4.xaml (17.2 KB)

Please check and let me know

Thanks,

15

selectRoute.xaml (13.5 KB)

Updated and Tested with above two cases, results are fine…! @SIIL_Tracking

Please confirm…Thanks!

16

Thanks @kadiravan_kalidoss …it worked like charm :grinning: :grinning:

1 Like
17

Thanks…your solution works…but I want single route only.

18

Hi @kadiravan_kalidoss
One thing here I want to mention is if the route have only tow district…suppose only “Ahmedabad/Barmer”…then it is showing error.

19

Hi @SIIL_Tracking,

What are all the possible inputs, like how many districts will be given as inputs - Please confirm!

Current flow will work for only three districts…we need to make it dynamic…!

I will update it as dynamic please wait…

Thanks!

20

Hi @kadiravan_kalidoss
Sorry, I am unable to provide you the exact possibilities of combinations as the number of district in variable may vary from 1 to 6/7 districts. But you can assume minimum as 1 and maximum as 8.