Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

steve_crosby
New Contributor

Data Modelling Issue

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.

Tags (3)
9 Replies
thomas_skariah
Contributor III

Re: Data Modelling Issue

Hi Steve,

You can take help from the attached application.

Regrads,

Tom

steve_crosby
New Contributor

Re: Data Modelling Issue

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

Have a look at the example.

Is this what you want.

Regards,

Kaushik Solanki

steve_crosby
New Contributor

Re: Data Modelling Issue

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

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

steve_crosby
New Contributor

Re: Data Modelling Issue

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

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

For your chart expression, can you use:

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

-Rob

steve_crosby
New Contributor

Re: Data Modelling Issue

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

Community Browser