Filter data table in a json file

Learn
1

Hi,

I have a work flow to read a json file and write into excel sheet. I now need to get the rows only after a specific date. Even i assign filter data data table activity its not getting filtered by date. Please help me in this. I have attached my work flowJsonToDatatable.xaml (10.5 KB)

Thanks in Advance

2

@Thulini_Dissanayake Have you checked in this way, if it works :

Capture
Capture1013×387 11.3 KB

If it doesn’t then we might need to use Linq :sweat_smile:

1 Like
3

Tried it and not working :frowning: Could you pls help me with this?

4

@Thulini_Dissanayake So the Operation is you want to remove the rows which have Release Date Less than the Mentioned Date, Is that right ?

5

Yes!

6

@Thulini_Dissanayake Can you check this workflow :
JsonToDatatable.xaml (13.4 KB)

If any error occurred or is not the Output you expect. Please Revert back

1 Like
7

Getting an error
Capture

8

@Thulini_Dissanayake Can you Correct that Column Name by removing the space between them in the Assign Statement that I have Given? :sweat_smile:

9

I didnt get any errors but, now its filtering the other way around I want to remove older dates from the date specified.
then I changed the sign and check then its not getting filtered

10

@Thulini_Dissanayake No the Sign needs to be the same :sweat_smile:, I forgot to add a NOT operator. Can you correct it as shown below :
Capture

*“ReleaseDate”

1 Like
11

Not getting filtered. This is a sample json file im using
sample1.json (9.6 KB)

@supermanPunch this is only a sample

13

@Thulini_Dissanayake Are you able to Deserialize the Json File , I am not able to Deserialize it :sweat_smile:

1 Like
14

@supermanPunch Yes, the file i’m using can be deserialized but its too heavy cant attach here so i posted only a part of my file here might be i have missed something when copying

15

@supermanPunch check this file

response.json (183.8 KB)

1 Like
16

@Thulini_Dissanayake I get the 3 rows in the Output File after Deserializing and applying the filter to the response.json file you have given, is that what you expected?

1 Like
17

@supermanPunch is it returning the rows thats date is greater than the given date. Should remove the rows with older dates than given date.then its the correct answer

For me its returning the same DT without filtering

18

@Thulini_Dissanayake After Converting the response.json file to a Datatable it contains only 3 rows and Since the 3 rows Satisfy the Condition that it is not Less than the Mentioned Date, Those three rows will remain as it is in the Filtered Datatable.

Can you me an example of the Rows that needs to be present in the Output , Since I have just a Sample json may be I am not able to recognise the right output and considering you have all the Data from the json File, you might be able to Show me the Correct output.

1 Like
19

@supermanPunch try to change the date range to satisfy the condition and test. then its not getting filtered

20

@supermanPunch need all the rows from 2019-12 and discard all other dates but according to the sample i send you can u try changing date and see…
Because in my file its not getting filtered atall

21

@Thulini_Dissanayake Can you use Write Range After Deserializing the json File into a Datatable and get the Whole Data in the Excel, in that way we can make out the Date values, and if it can be filtered or not.

To just Check if filter is working or not, I have reversed the Condition that is to get the rows which have less than the mentioned Date, and since no rows existed, It returned Empty Datatable.

1 Like