Looping through rows, assign a record ID, create a vector and finally cross-tab dataset

[emmanuel_varesio]

Hi there,

First of all Happy NY'21 !

I'm trying to read an input file with records separated by blank rows arranged in a long "portrait" mode.

Each record has two data structures:

a) an easily parsed format with a ":" separator for the first rows (e.g. NAME : ABCD)

b) a varying number of rows containing two numeric fields separated by a tab (a space in my attached example), the number of rows being described in the Num peaks field.

My needs are:

i) assign a unique RecordID for each record with a kind of while loop with the blank rows being the separators,

ii) create two new fields such as vectors of varying length for the numeric fields

iii) remove the rows with the numeric fields

iv) and finally convert the portrait data format to a wide landscape format (cross tab tool ?) in order to create a database with all these records.

I have attached an example with two records in the input format on the first XL tab and the output format I'd like to achieve before creating the database.

Any help/input from your side is highly appreciated.

Thanks in advance and stay safe.

Emmanuel

[AngelosPachis · Accepted solution]

Hi @emmanuel_varesio ,

Happy and prosperous 2021! I've mocked up a workflow that I think meets your requirements

Of course there are ways to do it with less tools, but I tried to keep it as "clean" as possible to be able to track what's going on and troubleshoot it in the future. Let me know if that works for you, I hope that helps.

Regards,

Angelos

[Tyro_abc · Accepted solution]

Hi

Happy New Year.

This is my solution

[emmanuel_varesio]

Hi @AngelosPachis,

Thank you for this detailed and very informative step-by-step workflow. I'll apply to my real dataset right now 👍

Best regards,

Emmanuel

[emmanuel_varesio]

Hi @Tyro_abc ,

Thanks for your solution too. I like the regexp function to parse the two numeric fields.

Best  regards,

Emmanuel