Formula to include missing information in output

[suds144]

Hi Everyone,

I need your help. I'm using Alteryx to combine feedback survey answers. However, some weeks are missing submissions and as a result when I put them into alteryx and combine the data they do not appear in the search. Is there a formula that will include the weeks with no data?

Input:

Output:

I just need the academic week to appear in the output so I can then use this information to create a dashboard (in tableau). Thank you.

[binuacs]

@suds144 

[suds144]

Thank you, why did you add a row count? I've tried replicating this but the new academic weeks are not showing in my data set,

[suds144]

What if I have several rows that are blank (not just three). Do I still use the formula tool?

[suds144]

Does anyone have any simpler workflows? I've tried this and it isn't working for me.

[Luke_C]

Hi @suds144 

Can you expand on the logic? It doesn't make sense to me. For example, the input has 2021/22, /23, /24, /25 but output is all 2021/22. Are there only 5 weeks in an academic year? 

[suds144]

Oh gosh, I just realised a mistake I made in this. The input is incorrect. It should read as follows:

*MW relates to Michaelmas Week
*LW relates to Lent Week

Output should be:

I need to include MW3 and LW3 into the output (they both had zero submissions)

Can anyone suggest an easier workflow to include the missing academic week?

[binuacs · Accepted solution]

@suds144 

[suds144]

Thank you! This is useful.

Everytime I try to add more numbers (e.g. 3,4,5) to the following formula: IIF(StartsWith([Academic Week], 'M'), 1,2)
It says it is malformed. Do you know why?

[binuacs]

@suds144 this formula creates an id field which later can be used to sort the records. Can you share your formula with 3 or 4, 5 you used?