topic Re: Set Analysis Prob in QlikView

Set Analysis Prob

Mon, 14 Jan 2013 13:39:33 GMT

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?

Re: Set Analysis Prob

Mon, 14 Jan 2013 14:12:42 GMT

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

Re: Set Analysis Prob

martin59 — Mon, 14 Jan 2013 14:23:10 GMT

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

Re: Set Analysis Prob

CELAMBARASAN — Mon, 14 Jan 2013 14:57:48 GMT

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

Re: Set Analysis Prob

Mon, 14 Jan 2013 15:54:06 GMT

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

Re: Set Analysis Prob

Mon, 14 Jan 2013 18:44:33 GMT

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

Re: Set Analysis Prob

Tue, 15 Jan 2013 06:58:26 GMT

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.

Re: Set Analysis Prob

Tue, 15 Jan 2013 13:05:30 GMT

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

Re: Set Analysis Prob

Tue, 15 Jan 2013 13:13:56 GMT

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.

Re: Set Analysis Prob

Tue, 15 Jan 2013 13:24:52 GMT

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

Re: Set Analysis Prob

Tue, 15 Jan 2013 15:16:31 GMT

no reference due to problems with the model

no big new table as 3 Anwers above

Another try:

One small new table:

QUALIFY *;

Fact:

LOAD Sum(Sales Amount) as Sales Amount,

Sales Year as Year

RESIDENT Sales

GROUP BY Year

JOIN

LOAD Sum(Invoice Amount) as Invoice Amount,

Invoice Year as Year

RESIDENT Invoice;

GROUP BY Year

UNQUALIFY *;

Has only 2*Count(Distinct Year) Entries.

Small enough?

Greets

Klaus