topic Re: Which method should I use...? in QlikView

Which method should I use...?

Fri, 04 Oct 2013 08:04:53 GMT

I have two tables;

The first is an Order Header file and

The second contains the Order Lines.

The only piece of information I am interested in from the Order Header table is the order status.

What I want to do is only import data from the Order Lines table WHERE the Order Header order status = 5 or 6.

Table One

Order number	Order status	...
12345	5	...
23456	3	...
45678	6	...

Table Two

Item	Qty	Order no.	...
Plum	100	12345	...
Orange	200	12345	...
Apple	300	12345	...
Apple	400	23456	...
Grapes	500	23456	...
Peach	600	23456	...
Fig	700	45678	...
Peach	800	45678	...
Apple	900	45678	...

Desired Result

Item	Qty	Order no.	...
Plum	100	12345	...
Orange	200	12345	...
Apple	300	12345	...
Fig	700	45678	...
Peach	800	45678	...
Apple	900	45678	...

Because I have a one to many relationship and I don't want any info in the model from Table Two, I have no idea which method I should be using.

Thank you for your help.

Oli

Re: Which method should I use...?

Fri, 04 Oct 2013 08:12:15 GMT

Hi,

I would do:

LOAD the first table in a temp table with the condition you want

LOAD the second table with the exists function : where exists([Order Number], [order No])

Drop the temp table

Fabrice

Re: Which method should I use...?

sujeetsingh — Fri, 04 Oct 2013 08:24:42 GMT

Load distinct and filter the data with conditions.

Re: Which method should I use...?

Gysbert_Wassenaar — Fri, 04 Oct 2013 10:11:05 GMT

Load all the data and add a listbox for Order status. Then simply select the order status values you're interested in.

Re: Which method should I use...?

Fri, 04 Oct 2013 10:18:45 GMT

As I am still very new to this I have a question from a best practice point of view.

If load time is not a factor (due to over night schedule) should I go down the route of Aunez's method to avoid too much data in the model but at the expense of the load time?

Thank you all for you guidance.

Oli

Re: Which method should I use...?

Fri, 04 Oct 2013 10:25:13 GMT

it depends on what the users want to see:

- everything (all Order status) : ListBox

- some part (because the other order status are meaningless): filter during the script

Fabrice

Re: Which method should I use...?

Gysbert_Wassenaar — Tue, 08 Oct 2013 15:37:38 GMT

Unless users shouldn't be allowed to see certain data at all or the amount of data causes performance problems, I'd say load all the data.