Combine data of two tables

[#1]

Dear all,

I have a table with the average revenue per user per year per insert year (year of registration):

InsertYear	Year	2009	2010	2011	2012
2009	-	€ 8,99	€ 20,02	€ 21,68	€ 21,34
2010	-	-	€ 11,00	€ 20,13	€ 20,78
2011	-	-	-	€ 7,98	€ 21,47
2012	-	-	-	-	€ 8,52
Totaal	-	€ 8,99	€ 11,98	€ 10,63	€ 13,18

And a table with the retention costs per user per year per insert year (year of registration)::

InsertYearCosts	YearCosts	2009	2010	2011	2012
2009	-	€ 2,00	€ 1,50	€ 1,00	€ 0,50
2010	-	-	€ 2,00	€ 1,50	€ 1,00
2011	-	-	-	€ 2,00	€ 1,50
2012	-	-	-	-	€ 2,00

The names of the year fields differ per table:

Year = YearCosts

InsertYear = InsertYearCosts

Because of the different names, I cannot do calculations on both. I would like to deduct the retention costs from the average revenue per user and display the results in a new table.

I tried to amend the load statement to set the names the same, But then I get an error. I researched set analysis and variables but it didn't get me any further.

Does anybody have a solution? All help is appreciated.

Best regards,

Dorine

[amit_saini]

Hi Dorine,

You can call InsertYearCosts as InserYear and YearCosts as Year, so that you can have a common key between two tables. Generally It should work, please share error if this is not working for you.

Thanks,

AMIT

[alexandros17]

When you load the second table do as follows:

load

...

YearCosts as Year,

InsertYearCosts as InsertYear,

...

From ...

In this way names will be the same ..

Let me know

[#4]

Dorine, you have two different types of granularity. The best Method is simply concatenate the data into one table with Flag or Just join the these tables on the Insert Year & Year  like below:

Concatenate Method:

FInalTable:

LOAD

InsertYear,

Year,

AvgRevnue as Amount

1 AS FLAG_TYPE

From RevunueTable;

Concatenate (FInalTable)

LOAD

InsertYearCosts AS InsertYear,

YearCosts AS Year,

CostRetntion AS Amount

2 AS FLAG_TYPE

;

For you Retention cost just : sum(<{FLAG_TYPE={2}}>Amount)

Join Method:

FInalTable:

LOAD

InsertYear,

Year,

AvgRevnue

From RevunueTable;

Join (FInalTable)

LOAD

InsertYearCosts AS InsertYear,

YearCosts AS Year,

CostRetntion;

You can either of method it depends on the other data fields on the table.

You can also create the link on composite key b/n InerstYear & Year b/n two tables

[#5]

Dear Alessandro,

When I did that, I got the loop-error (see attachment).

I am new to Qlikview and have no idea what to do next.

Any ideas?

Best regards,

Dorine

[#6]

I could rename the fieldname YEAR but not InsertYear because that field is also in another table.

In the attachment you find the formulas and table overview. Hopefully you have another solution 🙂

[#7]

I believe, the Key must be both Insert Year & Year.