topic Re: Data Modelling Issue in QlikView

Data Modelling Issue

steve_crosby — Sun, 14 Oct 2012 16:29:08 GMT

Hi All

I am having difficulty in mapping some data into QlikView and wondered if anyone could help, please

Basically I have 5 tables in SQL database that contain Customers, Price Lists, Rebate Header, Rebate Detail and Invoices.

My challenge is this Customers can have Rebates the rate of which is held in the Rebate Header, for Products maintained on the Price List, UNLESS a Product is maintained in the Rebate Detail table in which case that rebate rate is used.

I've attached an xlsx that represents some sample data.

Re: Data Modelling Issue

thomas_skariah — Mon, 15 Oct 2012 01:57:00 GMT

Hi Steve,

You can take help from the attached application.

Regrads,

Tom

Re: Data Modelling Issue

steve_crosby — Mon, 15 Oct 2012 10:40:42 GMT

Hi Tom,

Thanks for your assistance, but I still dont get the correct rebate values returned against the customers/products.

Some of your script has given me an idea of how I might solve this, so I'll give that a go later today.

Thanks

Steve

Re: Data Modelling Issue

kaushiknsolanki — Mon, 15 Oct 2012 11:08:27 GMT

Have a look at the example.

Is this what you want.

Regards,

Kaushik Solanki

Re: Data Modelling Issue

steve_crosby — Mon, 15 Oct 2012 11:41:44 GMT

Hi Kaushik

Thanks for your help with this, your solution seems to eliminate some customers and products from the solution, have you any ideas as to why that would be?

Thanks

Steve

Re: Data Modelling Issue

kaushiknsolanki — Mon, 15 Oct 2012 11:44:23 GMT

Steve,

They are not shown in the chart as there is no transaction is related to them.

So I have set the Supress Null values in chart and thus they are not seen.

Is the values are correct which you were looking for?

Regards,

Kaushik Solanki

Re: Data Modelling Issue

steve_crosby — Mon, 15 Oct 2012 11:53:50 GMT

I spotted the Supress Null values, but cant work out why we have null customers against some of the Invoice Numbers that have got customers against them in the Excel tables?

Any clues?

Re: Data Modelling Issue

kaushiknsolanki — Mon, 15 Oct 2012 11:57:56 GMT

Cause they are also linked to the rebate data.

And if you check you dont have the rebate data for those invoices.

Regards,

Kaushik Solanki

Re: Data Modelling Issue

rwunderlich — Mon, 15 Oct 2012 19:53:04 GMT

For your chart expression, can you use:

=alt(Rebate, [Default Rebate], 0)

-Rob

Re: Data Modelling Issue

steve_crosby — Tue, 16 Oct 2012 09:54:01 GMT

Hi All

I have managed to resolve the issue but have resorted to using the creation of a sql view as shown below

create view Rebates as

select b.price_list,a.customer,product_code,a.end_date, isnull(rebate,default_rebate) as rebate

from price_list b

left join rebate_header a on b.price_list=a.price_list

left join rebate_detail c on c.price_list=b.price_list and c.product=b.product_code

where b.product_code!=''

and a.status!='X'

Still wondering if this could have been acheived within QlikView without creating a sql view