how to dynamically filter on a column that is created in a crosstab

[davidhenington]

I have a set of numeric columns that are created in a crosstab.

1. I need to dynamically identify the new column which has a title equal to the min value (which is a string variation of a date) of the possible column titles. 

2. after identifying that column i need to filter out null values from that column (the easy part). 

in this example the column i want to dynamically select for filtering is 2019M01. 

i have fully flushed out the limitations of dynamic select with this use case. 

Thanks for your help Community! 

[seven · Accepted solution]

Hi @davidhenington 

before you Crosstab, you can Summarize to create a distinct list (Group By) of the fields which will be used as headers in the Crosstab. Then you can take that list, date parse the strings into actual dates, take the min of those dates, and solve your first need.

Why do you need to filter out nulls just from that column? That is just for my curiosity. For the solution to your second need, join the min value from step 1 to the pre-crosstab data, filter that dataset to remove nulls, then union back to the unjoined data and you're done.

It sounds like you're a more advanced user and what I wrote should get you there. If you're still struggling let me know and I'll mock up a workflow. (which I might still do anyway)

[seven]

Hi @davidhenington 

that's the best I could without data, sample or otherwise. Images aren't terribly helpful unfortunately. Please let me know if this solves your case.

[seven]

Hi @davidhenington 

alternatively, you could use the Field Info tool after the Crosstab to get a list of headers, apply the same sort of parsing and min logic, and then filter from there.

[davidhenington]

@seven thanks so much, you got me 80% of the way there! 

To answer your question: records without the first month are excluded because w/o that a comparison can't be achieved. 

Big lesson learned and one that I should already know, oh so well: if you start getting frustrated, just walk away. Do something else, ideally exercise IMO! 

As in this case, typically you'll find that you're overthinking things. 

Your tip to summarize and then join back prior to the crosstab was the hint I needed. Changing the format wasn't even necessary. Here's how it goes: 

1. Summarize to get min value

2. Inner join back to data set on min value. Only select columns within join tool that are used as grouping fields in subsequent crosstab. 

3. Inner join those grouping fields back to data set (join on each of those fields). 

4. Crosstab the output. 

here you see how I did this and the original method (built in ad-hoc style) so that i could compare: 

and viola: