left join to transpose data into a column; 2 at at time
I have a budget data table loaded into QlikView, then, for multiple metrics, I am moving the budget values into a single column which can then be grouped and summed in various reporting tables.
It is complex to describe this, please forgive, but what I need to do is add on special columns for each metric to handle various calculations such as ratios that do not sum.
In one instance, I need to do this for two metrics...to put a YTD value into a new column. One at a time, no problem, just left join the new column in. But in this case, I need to do more than one metric.
I have a join statement that looks like this:
LEFT JOIN (Budget)
load 'somecategory' as Metric_Category
,if(ACCT=6042001,'metric 1',
if(ACCT=6042003,'metric 2')) as Metric
, Entity as CorpKey
, sum([P1]) as Budget_1_YTD
, sum([P2]) as Budget_2_YTD
, sum([P3]) as Budget_3_YTD
, sum([P4]) as Budget_4_YTD
, sum([P5]) as Budget_5_YTD
, sum([P6]) as Budget_6_YTD
, sum([P7]) as Budget_7_YTD
, sum([P8]) as Budget_8_YTD
, sum([P9]) as Budget_9_YTD
, sum([P10]) as Budget_10_YTD
, sum([P11]) as Budget_11_YTD
, sum([P12]) as Budget_12_YTD
Resident Budget_Details
where CostCenter = (30131) and match(ACCT,6042001,6042003)>=1
group by if(ACCT=6042001,'metric 1',
if(ACCT=6042003,'metric 2')), Entity
;
The fields in common between Budget and Budget_Details are
Metric_Category, Metric, Entity/CorpKey
If anyone can see the flaw in this logic, thanks in advance...
I suspect the group by/if statement is problematic
