topic Join two crosstab table in QlikView

Join two crosstab table

Wed, 23 Jan 2013 19:36:17 GMT

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

Re: Join two crosstab table

Gysbert_Wassenaar — Wed, 23 Jan 2013 19:48:37 GMT

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.

Re: Join two crosstab table

Anonymous — Wed, 23 Jan 2013 20:39:01 GMT

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

Re: Join two crosstab table

Wed, 23 Jan 2013 21:05:53 GMT

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