Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I have two crosstab tables need join together.
Headcount2012 Table:
Div | month1 | month2 | month3 | month4 | month5 | month6 | month7 | month8 | month9 | month10 | month11 | month12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
div1 | 2 | 3 | 4 | 6 | 4 | 3 | 2 | 2 | 6 | 8 | 2 | 1 |
div2 | 1 | 3 | 4 | 6 | 6 | 7 | 8 | 1 | 2 | 3 | 4 | 5 |
div3 | 2 | 4 | 6 | 7 | 3 | 2 | 1 | 5 | 6 | 7 | 3 | 2 |
div4 | 1 | 2 | 3 | 4 | 5 | 4 | 3 | 5 | 6 | 2 | 1 | 4 |
Headcount2011 Table:
Div | month1 | month2 | month3 | month4 | month5 | month6 | month7 | month8 | month9 | month10 | month11 | month12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
div1 | 1 | 2 | 3 | 5 | 4 | 6 | 8 | 9 | 1 | 2 | 3 | 5 |
div2 | 2 | 3 | 5 | 6 | 4 | 7 | 3 | 2 | 1 | 5 | 6 | 2 |
div3 | 1 | 3 | 4 | 5 | 6 | 8 | 2 | 3 | 5 | 6 | 7 | 6 |
div4 | 2 | 4 | 6 | 7 | 8 | 9 | 1 | 2 | 4 | 5 | 6 | 7 |
div5 | 4 | 5 | 6 | 6 | 3 | 6 | 4 | 8 | 1 | 2 | 3 | 4 |
in 2012 has one division removed. I need to add them to dashboard and 2012 shows 4 divisions and 2013 shows 5 division.
Can anyone help me create a join strategy?
Thanks in advance,
Dust
I'd concatenate the two tables instead of joining them. Something like:
Data:
crosstable(Month,Value,2)
load 2012 as Year, * from ...headcount2012table...;
concatenate
crosstable(Month,Value,2)
load 2011 as Year, * from ...headcount2011table...;
You'll end up with a table named Data with four fields: Div, Month, Value and Year. You can then create charts as needed. See for example the two pivot charts in the attached example.
Hi Dust,
Can you Please give some clatrification,
Current year only you needed five divison and Previous all year's you need 4 divison.
Thanks
Thank you for your helpful answer, Gybert.
For the new year 2013, we have some division removed from our system. When I load those data like you did, The division grey out since we don't have data for those removed division current year.
My dashboard need to show prior year such as 5 division and current year 4 division. It may need some bridge table to join those tables.
Best regards,
Dust