Filter some limited not null values from so many columns one-by-one

[Saraabdi955]

Hi,

I have So many columns in a table and I want to select only 50, for example, not null values from each column automatically.
Thanks for helping me ...

[danilang]

Hi @Saraabdi955 

Can you post a small sample of your input and what you'd like your output to be like?

Dan

[Saraabdi955]

Hi @danilang 

main.png is the main test dataset and filtered.png is 2 selected records of each columns.
Thank you so much

[danilang · Accepted solution]

Hi @Saraabdi955 

Here are your two images side-by-side

Main Filtered

This was a fun one because what you appear to be doing is shifting the column values up to replace null values, so your output row 1 is composed of Name from input row 2 and City and Date from input row 1.  An interesting technique I've never come across before.

The workflow transposes the input data and remove the nulls.  It then builds new output row number and crosstabs to get a new data set.  The new set can have empty values in the last set of rows, so you transpose it and count the number of empty values per row and pass on the rows that have all values filled.  Use a filter to remove the rows from the new data set and you're left with

Note that you actually have enough data to make 3 complete new output rows as opposed to the 2 that you have in your sample

Dan

[Saraabdi955]

exactly right ...

Thanks a lot Dan @danilang