Select and generate DATA from different tables

Milo2009 — Thu, 03 Sep 2015 09:20:48 GMT

Hello everyone:),

I haven't much experience in QV for now, but I'm try to get familiar with it (already knowing SQL)...

But now I've a problem, where I'm not able to discover the solution.

My Steps until now:

I'm loading two tables from an Excel File (that works fine) with the following result:

Sales_Shop:

Shop

Week

Category_Nr

Sales

Shop	Week	Category_Nr	Sales
1	492015	10	5
3	492015	10
4	512015	12	8

So Shop 3 doesnt have any sales in Category 10 and Week 492015.

Shop

Ref

Shop	Ref
3	1
...	...

So Table 2 has the following function: Every Shop will be assigned a Reference Shop (Ref). In this case the Ref for Shop 3 is Shop 1.

Now I have tried to get a third table with sales for every shop. The following logic should be applied.

If Shop X has sales in Category_Nr Y and Week Z than this Sales should be taken in the new table. If the Shop doesnt have real Sales, than the Sales from the Ref Shop should be taken into account. The Result should look like this:

Shop	Week	Category_Nr	Sales
1	492015	10	5
3	492015	10	5
4	512015	12	8

So the red marked 5 in the last table are the Sales from Shop 1, which will be assigend to Shop 3 for this Week and Category_Nr, because Shop 3 doesnt have any real sales (look at table 1).

I have no idea to how to get this result, as I'm not so familiar with the syntax in QV:/

Thanks for all your help or advice:)

Re: Select and generate DATA from different tables

petter — Thu, 03 Sep 2015 22:37:55 GMT

Actually I posted something with a flawed logic ... so I deleted that and posted this which should work:

topic Re: Select and generate DATA from different tables in QlikView

Select and generate DATA from different tables

Re: Select and generate DATA from different tables