Hi,
I have the following data and attached a Workflow.
Act | unit | Status |
123 | abc | C |
567 | abc | O |
123 | def | O |
567 | ghi | C |
567 | jkl | C |
789 | mno | C |
890 | mno | O |
789 | pqr | O |
I use Summary and crosstab tools for this display:
Status | unit | 123 | 567 | 789 | 890 |
C | abc | 1 | |||
O | abc | 1 | |||
O | def | 1 | |||
C | ghi | 1 | |||
C | jkl | 1 | |||
C | mno | 1 | |||
O | mno | 1 | |||
O | pqr | 1 |
But what I need is the Status to display with the Act field, including if there are no values, like the following. Any Ideas?
Unit | 123_C | 123_O | 567_C | 567_O | 789_C | 789_O | 890_C | 890_O |
abc | 1 | 1 | ||||||
def | 1 | |||||||
ghi | 1 | |||||||
jkl | 1 | |||||||
mno | 1 | 1 | ||||||
pqr | 1 |
Solved! Go to Solution.
Interesting challenge but there is a fairly straight forward solution.
Scaffold your data using an append, summerize and join, this will identify combinations where you have counts, and those where you don't, in which case you create a count of 0.
Next, before you cross-tab, concat the status and the act fields together and then cross-tab as you have done.
Example solution attached.
Ben
Hi BenMoss,
thanks for the response. I applied the recommendation to my 1.8 million records, but it seems I am getting extra data. When I add a filter and look at the output of the formula, it looks like it added a phantom record.. Is the "count as zero" correct?
Thanks for the response ponraj, but the output is missing the column 890_C. I was hoping to create a column for the " "Act" even though there is not data where Status = C.
Hi,
sorry for the long delay. I was pulled away to another project. I am attempting both suggestions to my 1.7 million units, and I am seeing a few varying results. I need some time to review the results to ensure my data is correct.
Hi BenMoss and Ponraj,
both solutions work. I accepted BenMoss as solution because he was first.
Can I accept 2 solutions?
Mike
Yes. You can accept n number of solutions provided