Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
steve_crosby
Partner - Contributor II
Partner - Contributor II

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.

9 Replies
thomas_skariah
Creator III
Creator III

Hi Steve,

You can take help from the attached application.

Regrads,

Tom

steve_crosby
Partner - Contributor II
Partner - Contributor II
Author

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Have a look at the example.

Is this what you want.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
steve_crosby
Partner - Contributor II
Partner - Contributor II
Author

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
steve_crosby
Partner - Contributor II
Partner - Contributor II
Author

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?

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

For your chart expression, can you use:

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

-Rob

steve_crosby
Partner - Contributor II
Partner - Contributor II
Author

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