topic Re: Connecting two Independent tables through an expression in Qlikview FrontEnd in QlikView

Connecting two Independent tables through an expression in Qlikview FrontEnd

Anonymous — Wed, 08 Nov 2017 09:45:51 GMT

Hi All,

I have below two tables with the following data. And for some technical reasons these tables are not connected in the model and there is no chance to connect them also.

Table1:

Opp Number	Sales price
2234	100
5577	200
6746	450

Table2:

Old Opp Number	Old Sales price
2234	500
5577	700
6746	850

Now i would like to display in front end like below.

Opp Number	Sales price	Old Sales price
2234	100	500
5577	200	700
6746	450	850

I was trying the below expression but it working only when i select an opp number, but i wanted show them without any selections.

Dimesion :

Opp Number

Expressions:

Sales price : Sum([Sales price])

Old Sales price: Sum({1< [Old Opp Number]=p({$}[Opp Number]) >}[Old Sales price]))

Would be great if someone can help on this..

Re: Connecting two Independent tables through an expression in Qlikview FrontEnd

Or — Wed, 08 Nov 2017 09:57:24 GMT

There's probably a way to do this with set analysis, but the easiest (to me) way is:

sum(if([Old Opp Number] = [Opp Number], [Old Sales price]))

That said, I don't really recommend this approach - why not attach the old sales price to the opp number at the script level? You don't have to actually connect the tables, just bring in the one extra column using a mapping load or a join load.

Re: Connecting two Independent tables through an expression in Qlikview FrontEnd

Anonymous — Thu, 09 Nov 2017 04:46:42 GMT

Thanks for your Reply.

The suggestion to use if condition is not working.

We have few restrictions not add the old sales price to Table1, because old sales price is being used in other places with other dimensions. If i add old sales price to Table1, it is like having same values in two different tables. If we do not have any other solution we will think to go for this approach.

Re: Connecting two Independent tables through an expression in Qlikview FrontEnd

Anil_Babu_Samineni — Thu, 09 Nov 2017 05:08:06 GMT

Try to change second table of field to same as first table like below

Table1:

Load * Inline [

Opp Number	Sales price
2234	100
5577	200
6746	450

];

Join

Table2:

Load [Ola Opp Number] as [Opp Number], [Old Sales price] Inline [

Old Opp Number	Old Sales price
2234	500
5577	700
6746	850

];

In front end, Create straight table

Dimension - [Opp Number]

Expressions are

1) Sum([Sales Price])

2) Sum([Old Sales Price])

Re: Connecting two Independent tables through an expression in Qlikview FrontEnd

Anonymous — Thu, 09 Nov 2017 09:17:55 GMT

Thanks for your reply.

This is going to make a join between two tables on opp number, which we can't do as i was explaining above.