Check if a value is within a dynamically created row, if so set a value in another column

Question

I have dynamically created columns called

"ShelfxZN1", ShelfxZN2, ShelfxZD1, ShelfxZD2" and so on.

For everyone of those dynamically created columns I have a corresponding

"StockxZN1, StockxZN2, StockxZD1, StockxZD2" columns and so on.

I don't know in advance the number of those columns. I only know that the patterns stays the same.

Problem: If within a row of the ShelfColumns any value is found, I want that the value 0 is set for the corresponding Stock column.

Example RAW data:

ShelfxZN1, ShelfxZN2, ShelfZD1, ShelfxZD2, StockxZN1, StockxZN2, StockxZD1, StockxZD2
ShelfA, NULL, ShelfC, ShelfD, NULL, NULL, NULL, NULL

After the transformation:

ShelfxZN1, ShelfxZN2, ShelfZD1, ShelfxZD2, StockxZN1, StockxZN2, StockxZD1, StockxZD2
ShelfA, NULL, ShelfC, ShelfD, 0, NULL, 0, 0

So in a nutshell: When the shelf is NULL, don't do anything. If anything is in it, set 0 for the corresponding stock.

I don't have a clue how to dynamically do it :/

Answer 1

You can use Dataflow derived column pattern to achieve your requirement.

For this, for every Shelfx column, there should be a Stockx column.

This is the sample data that I took with NULLs.

ShelfxZN1   ShelfxZN2   ShelfZD1    ShelfxZD2   StockxZN1   StockxZN2   StockxZD1   StockxZD2
ShelfA          NULL    ShelfC      ShelfD      NULL        Hi          NULL            NULL
NULL            data2   data3       NULL        NULL        NULL        NULL            NULL
NULL        col3data2   NULL        col4data4   Hi          NULL        NULL            NULL

Add a derived column transformation and create a derived column pattern with below expressions.

condition - startsWith(name,'Shelfx')
column name - concat('Stockx',replace($$,'Shelfx',''))
column value expression - iif(isNull($$),toString(byName(concat('Stockx',replace($0,'Shelfx','')))),'0')

Here, in the column value expression, you need to wrap the byName() function with your desired data type but ensure that the data types of the iif -> true expression and iif->false expression are same.

It will give the desired result upon dataflow debug.