topic Re: Adding calculated column to concatenated tables in QlikView

Adding calculated column to concatenated tables

Mon, 26 Aug 2013 18:17:04 GMT

Hello, all.

I am struggling with how to add columns to concatenated tables in order to calculate deltas between columns.

So here is a simplified example of what I want:

Table A has Category and Spend for Month A
Table B has Category and Spend for Month B

What I want is to create a table box object with the following:

Category, Spend for Month A, Spend for Month B, Delta

I have everything but the Delta part using the following code:

Month1:

sql select category, month_a_spend
from month_a_table

Month2:

sql select category, month_b_spend
from month_b_table

This gives me everything but the Detla column. In SQL, i would do this by joining Month1 and Month2 and adding calculations as columns. And I tried to achieve this with QlikView but couldn't and none of the examples I found work.

Help, please. Many thanks in advance.

Re: Adding calculated column to concatenated tables

jpapador — Mon, 26 Aug 2013 18:25:47 GMT

You could do it in the script but it would be faster to use a straight table instead of a table box. Make your dimension category and you expressions:

1. Sum({$<Month={'A'}>}Spend)

2. Sum({$<Month={'B'}>}Spend)

3. Sum({$<Month={'A'}>}Spend) - Sum({$<Month={'B'}>}Spend)

That will create the table you are looking for

Re: Adding calculated column to concatenated tables

Mon, 26 Aug 2013 18:40:59 GMT

Thanks! That did it!!

Re: Adding calculated column to concatenated tables

43918084 — Wed, 25 Dec 2019 05:14:24 GMT

THANKS.
may I know where I should put the calculated script?
Load
A. As t1.a
From t1
Load
A as t1.a
From t2