Set Analysis Prob

[#1]

I do have the following dataset:

Sales

     -Sales Amount

     -Sales Year

Invoice

     -Invoice Amount

     -Invoice Year

I want to create the following table:

                              2012     2011     2010     2009

Sales Amount          28          33       25          5

Invoice Amount         33          28       26         9

Sales and Invoices are linked by an ID. But I want the sales and invoice of that specific year. Based on Invoice Year and Sales Year.

How can I solve this?

[#2]

Hi Elke,

simply rename Sales Year and Invoice Year to Year and they will be associated via QVs natural join.

A bit more elegant and performant would be creating a single fact table:

Fact:

LOAD Sales Amount, Sales Year as Year

RESIDENT Sales

JOIN

LOAD Invoice Amount, Invoice Year as Year

RESIDENT Invoice

The syntax works, if you have the tables already already in QV named as

Sales and Invoice. Otherwise load them first.

BTW I don't see the ID you mentioned, where is it?

Greets

Klaus

[martin59]

Hi,

You don't have to use Set Analysis to do this.

But you must prepare your datamodel to do this.

Best way should to create only one table with sales and invoices.

Hope that helps you,

Martin Favier

[CELAMBARASAN]

Pivot chart

Have a dimesnion as

=ValueList('Sales', 'Invoice')

and YearField

Use expression as

if(ValueList('Sales', 'Invoice'))='Sales', Sum(Sales),Sum(Invoices))

Drag the year field to be show in horizontal

[#5]

Hi Elke,

think this is not necessary.

Just create a chart 'pivot table'

use Year as Dimension,

Sales Amount and Invoice Amount as Expressions

drag year to the top (as column) and the Amounts to the left (as rows)

Greets

Klaus

[#6]

Or do you need

Amount Type           2012     2011     2010     2009

Sales Amount          28          33       25          5

Invoice Amount         33          28       26         9

as a table in your script?

Ciao from Munich

Klaus

[#7]

Thanks for the replies. But all these solutions can't work for me.
The following datamodel is part of a much bigger model.

Sales:

-SalesDate

-SalesYear

-SalesAmount

-ID

Invoice:

-InvoiceDate

-InvoiceYear

-InvoiceAmount

-ID

Can't link on SalesYear with InvoiceYear because of the bigger datamodel(+/- 75 tables) and the figures will not be correct anymore for other graphs.

And it must be dynamic. So no hardcoded years.

[#8]

Hallo Elke,

then qualify the attribute names of the new table, so they won't reference to the existing tables of your model:

QUALIFY *;

Fact:

LOAD Sales Amount, Sales Year as Year

RESIDENT Sales

JOIN

LOAD Invoice Amount, Invoice Year as Year

RESIDENT Invoice;

UNQUALIFY *;

If you need to have an explicite reference from that new table to somewhere else, just unqualify that attribute as follows (Year in this example):

QUALIFY *;

UNQUALIFY Year;

Fact:

...

Grüße

Klaus

[#9]

So what you are saying is duplicate data to generate 1 chart?

This is a possible solution but I don't believe this is the correct one. Because tables are very big and qlikview file will grow still bigger when adding 1 aggregation table for 1 chart.

[#10]

Hi,

I don't know your model and the load within it.

Can you show a part of the model via 'table viewer'?

I prefer loading data a defined fact tables,

but I think just referencing the two tables could also do the trick:

Just change the load statements of Sales (Sales Year as Year) and Invoice (Invoice Year as Year).

And then use the 3 attributes in a pivot tabel as mentioned above.

Klaus