dynamically fetch the records based on attribute priority

[alteryx_user25]

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

[aatalai]

@alteryx_user25 can you please provide an example/dummy data and desired output?

[aatalai]

sorry just missed the bit at the end

[alteryx_user25]

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 id	col3	col1	col5	col6	col4	col2
1			sample	sample		value
2						value
3		sample
4		sample
5		sample
6		sample
9		sample
10		sample
13						value
14						value
15		sample	sample	sample
25						ABC
26						ABC
27						ABC
28						ABC
40	ABC
41	ABC
42	ABC
45	ABC
46	ABC
47						ABC
49	ABC		BBB
51					sample
52					sample
53					sample
54					sample

Sample input:

party id	col3	col1	col5	col6	col4	col2
1			sample	sample		value
2						value
3		sample
4		sample
5		sample
6		sample
7	ABC
8	ABC
9		sample
10		sample
11		sample
12		sample
13						value
14						value
15		sample	sample	sample
16		sample
17		sample
18		sample
19		sample
20		sample
21
22					value
23					sample
24					sample
25						ABC
26						ABC
27						ABC
28						ABC
29	ABC
30	ABC
31		sample
32		sample
33					sample
34					sample
35	ABC
36	ABC
37		sample
38		sample
39	ABC
40	ABC
41	ABC
42	ABC
43		sample
44		sample
45	ABC
46	ABC
47						ABC
48						ABC
49	ABC		BBB
50	ABC
51					sample
52					sample
53					sample
54					sample
55		sample				sample
56		sample				sample
57	ABC
58	ABC
59	ABC
60	ABC
61	ABC
62	ABC
63	ABC
64	ABC
65		sample
66		sample
67	ABC
68	ABC
69		sample
70		sample
71		sample
72		sample
73		sample
74		sample
75	ABC
76	ABC
77	ABC
78	ABC
79	ABC
80	ABC
81	ABC
82	ABC
83	ABC
84	ABC
85		sample
86		sample
87	ABC
88	ABC
89		sample
90		sample
91		sample				sample
92		sample				sample
93		sample
94		sample
95					sample
96					sample
97					sample
98					sample
99	ABC
100	ABC

[mzak89]

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]

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.