Help with derived columns please

Question

Help with derived columns please

sam nick 366

Hello,
In my incoming feed, i have an array and i'm loading the fields into my sql DB as below

Under the CurrentAssetMax field , the initial values within the "square brackets" are the same as ID and then the individual values in the next square bracket set and followed by their respective values in the last square brackets. For ID = 2678734, the DOCID value in the second value within the last square bracket. i.e if it comes the third, then the third value is the corresponding value.

My end goal is to achieve the below.

I was thinking of using a derived column in the dataflow using instr, substring etc. But unable to get the right formula. Please can you help me with the formula (or) a better recommendation to get the end result

1 answer

Your answer

Answer 1

MartinJaffer-MSFT 26,161

Hello @sam nick and welcome back again. Here are my initial thoughts (not final solution).

I see the format is of [numbers I don't care about] [comma separated tags] [numbers of same order of tags]

So the first step is to isolate each collection (stuff in brackets).
Second step is to discard the first collection
Third step is to either loop over the length of the remaining collection, or iterate over their items.
During the loop/iteration we need to somehow put the values into new columns.

For the first step, I am thinking of splitting on ][ and then cleaning up the remaining ]. I would use dropright function to remove the last ] character.
For the third step I am thinking of using maploop( length of tags collection).

If the number of tags is fixed and small, a simpler brute-force solution is possible.
Similar beginning steps, separating and discarding first collection.
Then do something like "if column name is found in tag collection, then return value at the index it is found in tags"

I'll work out the particular details today or tomorrow.

MartinJaffer-MSFT 26,161 Reputation points

2021-12-08T21:01:25.903+00:00
So, I worked out preliminary pieces, but ran into a couple snags. I thought there was a function which would search in an array and tell me the index of the first matched item. I can't seem to find it. This is making me re-think my approach.

The pieces I have worked out:

To fetch the array of tags: split(replace(split({ CurrentAssetMax},'][')[2],'"'),',') To fetch the array of values: split(rtrim(split({ CurrentAssetMax},'][')[3],']'),',') To get the count of items / length of array: size(split(split({ CurrentAssetMax},'][')[3],','))

So I am looking into subcolumns, complex data types, and mapLoop and mapIndex functions as work-around possibilities.
@sam nick

MartinJaffer-MSFT 26,161

I have some more progress, but I get the feeling I am doing this the hard way.

Turn tags and values into a json like string:
replace(reduce(mapIndex(tags, concat('"', #item, '":', values[#index])),'', #acc + ',' + #item , concat('{',#result,'}')),'{,','{')

Turn tags and values into an association:
keyValues(tags,values)

So if I can parse and then flatten, or otherwise map the sink...

sam nick 366 Reputation points

2021-12-09T19:28:50.733+00:00

@MartinJaffer-MSFT Thank you for the update. It does give me some idea, and I am looking forward to your solution
MartinJaffer-MSFT 26,161 Reputation points

2021-12-09T20:32:35.56+00:00

Can you tell me whether the expected columns are fixed, or drifting? Like, is the list of possible tags finite? The way things look, hardcoding the column names will be required at some point. @sam nick
sam nick 366 Reputation points

2021-12-10T15:55:19.003+00:00

@MartinJaffer-MSFT Yes, the values in the second set of [ ] are finite. What you see in the third row are the only values we can and will expect. The order can change but the list of values are finite.

i.e We can expect values for only these components. Either of these or none or all of them.

BookID
Message
DeptID
DocID

Share via

Help with derived columns please

1 answer

Your answer