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

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

6 Replies
amit_saini
Honored Contributor III

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:

load

...

YearCosts as Year,

InsertYearCosts as InsertYear,

...

From ...

In this way names will be the same ..

Let me know

Not applicable

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:

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

Not applicable

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

Not applicable

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

Not applicable

Re: Combine data of two tables

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