dynamically fetch the records based on attribute priority

Question

I have an excel file as input where there are N number of rows with party_id and 6 other attributes. This is linked to one sub workflow where we get the number of rows we need to pull(it could be 50,60,70 or any other values. Here we need to pull dynamically rows(lets take 54 but it can differ), where maximum records should come from col1 and col2 where non null values are present for these columns, then col3 and col4, and last col5 and col6. Hence, in this case, 17 records came from col1, 15 records from col2, 8 records from col3, 6 records from col4, 4 records from col5 and 4 records from col6. Need an alteryx workflow solution to dynamically handle this.

Priority of attributes decrease as we go down:

P1: col1 and col2

P2: col3 and col4

P3: col5 and col6

Sample data for reference.

Party id, col1, col2, col3, col4, col5, col6

12345, val1, val2, val3, val4, val5, val6

23456,null,null,val03,val04,null,val06

alteryx_user25 · Answer

The result can vary, but what I am trying to explain is we cannot pull records for non null values from just top priority columns, it has to be picked from other lower priority columns too but yes, the frequency of non null values should decrease as compare to the higher priority columns. For example, if we have fetched 10 non null records each from top priority columns then lower priority should fetch lesser records like 6 records maybe. The idea here is lets see we need to pull 50 records then col1 should have least non null values and col6 will have maximum non null values, but col6 cannot be completely filled with all null values. Hope this explains your question.

mzak89 · Answer

Hello!

I do not get it.

Why party ID 11 and 12 have been excluded from the output?

or 15-20? They have values in col1, which is the top priority

alteryx_user25 · Answer

To make it more clear, the output should have some values in all 6 attributes, meaning one attribute cannot be left with all null values.

Col1 has 7 non null values as it is part of Priority1 columns.

Col2 has  9 non null values as it is part of Priority1 columns.

Col3 has 6 non null values as it is part of P2.

Col4 has 4 non null values as it is part of P2.

Col5 has 3 non null values as it is part of P3.

Col6 has 2 non null values as it is part of P3.

Note P1 columns should have maximum non null values, then P2 and at last P3. This should be dynamically pulled basis the number of records.

output should look like below.

party idcol3col1col5col6col4col21  samplesample value2     value3 sample    4 sample    5 sample    6 sample    9 sample    10 sample    13     value14     value15 samplesamplesample  25     ABC26     ABC27     ABC28     ABC40ABC     41ABC     42ABC     45ABC     46ABC     47     ABC49ABC BBB   51    sample 52    sample 53    sample 54    sample

Sample input:

party idcol3col1col5col6col4col21  samplesample value2     value3 sample    4 sample    5 sample    6 sample    7ABC     8ABC     9 sample    10 sample    11 sample    12 sample    13     value14     value15 samplesamplesample  16 sample    17 sample    18 sample    19 sample    20 sample    21      22    value 23    sample 24    sample 25     ABC26     ABC27     ABC28     ABC29ABC     30ABC     31 sample    32 sample    33    sample 34    sample 35ABC     36ABC     37 sample    38 sample    39ABC     40ABC     41ABC     42ABC     43 sample    44 sample    45ABC     46ABC     47     ABC48     ABC49ABC BBB   50ABC     51    sample 52    sample 53    sample 54    sample 55 sample   sample56 sample   sample57ABC     58ABC     59ABC     60ABC     61ABC     62ABC     63ABC     64ABC     65 sample    66 sample    67ABC     68ABC     69 sample    70 sample    71 sample    72 sample    73 sample    74 sample    75ABC     76ABC     77ABC     78ABC     79ABC     80ABC     81ABC     82ABC     83ABC     84ABC     85 sample    86 sample    87ABC     88ABC     89 sample    90 sample    91 sample   sample92 sample   sample93 sample    94 sample    95    sample 96    sample 97    sample 98    sample 99ABC     100ABC