Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Sum values in function of dates

Hi everyone,

I need some helps to resolve this problem :

I got 2 tables :

Table A :

Key          Date1          Value1

1               2010          5

2               2010          8

3               2011          7

4               2012          6

5               2013          2

Table 2 :

Key          Date2          Value2

1               2013          12

2               2011          10

3               2010          1

4               2010          7

5               2011          6

Resulat needed :

Date          Value1          Value2

2010          5+8               1+7

2011          7                   10+6

2012          6                   0

2013          2                   12

I need to sum value1 and value2 in function of year (from field Date1 and Date2). For this, I need to "ignore" the Key. Can I do it into an expression ? I can't modify my datamodel. I need to keep the key and I can't use date fields as key.

Thanks for helping

EDIT : Easy thank IF but very slow. I'm looking for SET ANALYSIS

4 Replies
Not applicable

Re: Sum values in function of dates

Can you make your dimension for dates to be Date for both instead of Date1 and Date2?

if so make  a straight table with date as a dimension, then sum(Value1) and sum(Value2) for the expressions

ecolomer
Honored Contributor II

Re: Sum values in function of dates

In the file you have my proposal

Date_2File.png

MVP & Luminary
MVP & Luminary

Re: Sum values in function of dates

Hi,

Try like this

Data:

LOAD

Key,

Date1 AS Date,

Value1

FROM TableA;

Concatenate(Data)

LOAD

Key,

Date2 AS Date,

Value2

FROM TableB;

Now in Straight table use

Dimension: Date

Expression : Value1 = Sum(Value1)

     Value2 = Sum(Value2)


Hope this helps you.


Regards,

Jagan.

Not applicable

Re: Sum values in function of dates

Thanks but change the modele isn't possible. Otherwise, that would not have been a problem

That why i'm looking for an advance expression with set analysis. Not sure we can.