6 Replies Latest reply: Feb 14, 2014 10:22 PM by Srikanth P

Combine data of two tables

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

• Re: Combine data of two tables

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

• Re: Combine data of two tables

When you load the second table do as follows:

...

YearCosts as Year,

InsertYearCosts as InsertYear,

...

From ...

In this way names will be the same ..

Let me know

• Re: Re: Combine data of two tables

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

• Re: Re: Re: Combine data of two tables

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 :-)

• Re: Combine data of two tables

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

• Re: Combine data of two tables

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:

InsertYear,

Year,

AvgRevnue as Amount

1 AS FLAG_TYPE

From RevunueTable;

Concatenate (FInalTable)

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:

InsertYear,

Year,

AvgRevnue

From RevunueTable;

Join (FInalTable)

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