I have a log with multiline records. How could I concatenate a few lines into one line?

[TrifactaUsers]

Hi! I have a question about the product features.

I have a log that looks like this - see attachment. This log contains strings with the different structure and the different length. It is a real example from the loyalty management system. But this log is depersonalized :)

I want to normalize this log and then process it. For postprocessing I need a log where all of records start from timestamp. And each record corresponds to one line.

An example of multiline record (timestamp + params + xml) you can see in the lines 6-18 of attachment.

I have a lot of such records in my real log.

How could I concatenate such records into one line?

UPD Can be an arbitrary number of singleline records between multiline records. There can be an arbitrary number of lines in each multi-line record. A sign of the end of the record is the end of the XML block.

[TrifactaUsers]

do you think below format will work for you? if not, could you please provide a sample that how are you expecting your result set to be?? let me know your comments on attached...

[Trifacta_Alumni · Accepted solution]

@Sridar Krishnamoorthy? the LIST function doesn't guarantee ordering which may be a non starter in this use case (you want to preserve the order of the description fields).

@Alexey Nikolaev? - there's a more elegant solution to this problem in more recent versions of Trifacta where you can use the newly added ROLLINGLIST() window function to collect the description fields into one record preserving order but I believe you're on the Desktop version which is a couple of releases behind. I can add you to the beta list for our free SaaS beta which is on the current version, you'll get an email later this week with instructions on how to get going. I've attached the recipe I used in that version to get the dataset all cleaned up.

In the meantime there's a more quick and dirty solution that should suffice in the desktop version. In order to do this you'll need to import your dataset without detecting structure. In the import dataset screen click on the 'Edit settings' button after you've selected your dataset and then uncheck 'Detect structure'

Now, when you add a recipe you'll see that there's an existing step starting with "Break into rows"

If you edit this step and change the on parameter from \n to \n2 and then save. Then paste in the following transform:

set col: column1 value: if(left(column1, 1) != '2', merge(['2',column1]), column1)

which will go and re-add the 2 that was removed by the row break for any record missing the leading year number.

Hope that helps!

Alon

[TrifactaUsers]

@Alon Bartur (Trifacta) - Thank a lot. That works!

"I can add you to the beta list for our free SaaS beta" - it will be very helpful for me.