Filter datatable based on conditions

Help
,
1

Hi,

This is my datatable:

“CNY Manufacturing PMI (JUL)” High H 49.7 49.6 49.4

“AUD Consumer Prices Index (YoY) (2Q)” High H 1.60% 1.50% 1.30%

“EUR German Unemployment Change (000’s) (JUL)” High H 1.0k 2.0k 0.0k

“EUR Euro-Zone Gross Domestic Product s.a. (YoY) (2Q A)” High H 1.10% 1.00% 1.20%

“EUR Euro-Zone Consumer Price Index Core (YoY) (JUL A)” High H 0.90% 1.00% 1.10%

“CAD Gross Domestic Product (YoY) (MAY)” High H 1.30% 1.50%

“USD FOMC Rate Decision (Upper Bound) (JUL 31)” High H 2.25% 2.50%

“USD FOMC Rate Decision (Lower Bound) (JUL 31)” High H 2.00% 2.25%

Is there a way to filter the table using a few conditons:

  • the table is dynamic, it changes everyday
  • only 3 events are needed from the table
  • the 3 events are determined by the importance of its currency, which goes in the order USD > EUR> CNY> AUD> CAD
    • so if the table contains USD, USD will be the first to be extracted
    • if it doesn’t, it will look for EUR
  • However, a currency cannot be duplicated
    • meaning if there are a few USDs in the table, only one (random ) is required

Thanks :pray:

2

Hi,

There is a possibility to filter data table using the activity filter data table…Using this activity you can check for some condition, Keep required column values, Delete the columns which are not required!

For example if currency is your first column you can filter using column number with contains operator and writing different currencies with comma seperated.

3

the column part doesnt work…
it worked with one value, but not more than one.
this is what I did: " ‘USD’ , ‘EUR’ "

4

I was actually thinking of using a for each/ while loop to loop through the data table and break it when 3 values have been collected…

But I am not entirely sure how…

5

Thanks for providing the data, may i clarify that the output data for this case would be:

“USD FOMC Rate Decision (Upper Bound) (JUL 31)” High H 2.25% 2.50%
“EUR German Unemployment Change (000’s) (JUL)” High H 1.0k 2.0k 0.0k
“CNY Manufacturing PMI (JUL)” High H 49.7 49.6 49.4

OR
“USD FOMC Rate Decision (Upper Bound) (JUL 31)” High H 2.25% 2.50%
“USD FOMC Rate Decision (Lower Bound) (JUL 31)” High H 2.00% 2.25%
“EUR German Unemployment Change (000’s) (JUL)” High H 1.0k 2.0k 0.0k

Cheers

6

“USD FOMC Rate Decision (Upper Bound) (JUL 31)” High H 2.25% 2.50%
“EUR German Unemployment Change (000’s) (JUL)” High H 1.0k 2.0k 0.0k
“CNY Manufacturing PMI (JUL)” High H 49.7 49.6 49.4

this would be the desired output

thanks a lot :slight_smile:

7

@bhe98

There you go.
I created a new datatable and by looping through an order array 3 times, i hope you have achieved your desired output.

Keep in mind, for each currency, only the first matched row of the currency will be added to the new datatable.

Currency.xaml (12.8 KB)

1 Like
8

Hi,

Thank you so much for your help.

However, it still doesn’t work on mine as I feel that there is a slight error in the for each row box.

The current condition is: order.ToString = row(“Country”).ToString

In my case, the order wouldn’t match the country as the value contains more than just the name of the country like “CNY Manufacturing PMI (JUL)”

Should it be row(“Country”).ToString.Contains(order.ToString)?

Thanks

9

I had thought that these were separate columns. Either way, you may try:
order.ToString = row(“Country”).ToString.Split(" "c)(0)
This splits the string by blanks and compares the first word.

PS. You may want to avoid using row(“Country”).ToString.Contains(order.ToString)
If your field is something like “CNY Manufacturing PMI AMATEUR (MAY)”, it will match the case where it contains “EUR”

10

Would it still be order.ToString = row(“Country”).ToString.Split(" "c)(0), if my field is “11:00 CNY Manufacturing PMI (JUL)” ?

11

Hi @bhe98,

You can use OrderBy & ThenBy functions with StartsWith method in LinQ.

  1. OrderBy and ThenBy —> to give priorities
  2. StartsWith —> to give condition. like —>
    OrderBy(Function(a) (a("Country").ToString.StartsWith("CAD"))).ThenBy(Function(b) (b("Country").ToString.StartsWith("AUD"))) and so on.

I’m attaching a zip having sample workflow for your better understanding. :slight_smile:

group_Priority.zip (18.9 KB)

12

No it will not, in that case you may use order.ToString = row(“Country”).ToString.Split(" "c)(1) or row(“Country”).ToString.Contains(order.ToString)

Kindly remember to mark whichever post that has solved your issue, thanks.

13

thanks for your help :slight_smile:

1 Like
14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.