Cleansing Data

[Simon1187]

Hi everyone! @Qiu 

I am trying to split a Jason file and count how many  "Discount" words are in the string and how can I split them into columns. 

the string is like below: 

[{"$type":"OccurrenceDiscount","Occurrences":6,"AppliedCount":6,"AppliedOn":"2022-07-16T00:00:00","Cancelled":null,"CancelledStamp":null,"CancelledReason":null,"Discount":{"Value":165.0,"Type":0},"Reason":"Approved Mick - Timeframe Blow out","Started":"2022-01-18T00:00:00+11:00","StartedStamp":{"On":"2022-01-18T09:24:36.7940091+11:00","By":"TMOODLEY"}},{"$type":"OccurrenceDiscount","Occurrences":4,"AppliedCount":4,"AppliedOn":"2022-07-16T00:00:00","Cancelled":null,"CancelledStamp":null,"CancelledReason":null,"Discount":{"Value":163.9,"Type":0},"Reason":"rick","Started":"2022-03-19T00:00:00+11:00","StartedStamp":{"On":"2022-03-19T02:03:34.822948+11:00","By":"MWRIGHT"}}]

and finally, I want to get the amount of value into columns. 

The string is in RecurringDiscounts_Data column. 

would you be able to assist me with this?

Thanks, 

Simon

[grazitti_sapna]

@Simon1187, try this.

Thanks!

[Qiu]

@Simon1187 
I am late but just give my try here.

[Simon1187]

@Qiu,@grazitti_sapna, Hi everyone! 

Thanks. However, when uploading the data set the result will be different. 

Can you please check the sample data set? 

Thanks, 

[grazitti_sapna]

@Simon1187, try this with your new sample file.

Thanks!

[Simon1187]

Hi @grazitti_sapna , 

Thanks. However, it does not work. 

[grazitti_sapna]

@Simon1187 what is the exact output you are looking for as per the workflow shared with you seems to have achieved it.

Please share your desired output.

[Simon1187]

Hi @grazitti_sapna , @Qiu 

Sorry for the late reply. 

some of the data is like: 

[{"Created":"2022-07-13T07:22:07.2517737+10:00","UserName":"JTELFORD","Tags":null,"Comment":"Adjusted from '385.00' to '0': BE Migration"}]

would you be able to assist me with this?

Thanks, 

Simon

[grazitti_sapna]

@Simon1187, can you share a sample file?

Thanks!

[Simon1187]

Hi @grazitti_sapna 

Can you please find the attachment? 

I am trying to split the column "History_Data_Data" into columns and if History_Data_Data contains two discounts then I would like to put it in another row in beneath. 

Thanks in advance!

Thanks, Simon

Thanks, 

Simon