topic Re: Re: Find missing order in QlikView

Find missing order

Sat, 15 Nov 2014 17:12:16 GMT

Hi!

I have a table with fields looks like this:

Customer (Customer number)

Active (Yes or No)

Date

Order (Ordernumber)

I have put these in a chart like the one shown below.

Date	Customer	Active	Order
2014-01-01	123456	Yes	1
2014-01-01	234567	Yes	1
2014-02-01	123456	Yes	2
2014-02-01	234567	Yes
2014-03-01	123456	Yes

What I would like to do is to have a listbox with the available dates, select a date and the customers active at this date with no orders placed (field blanked in example above) to be shown.

Re: Find missing order

morgankejerhag — Sat, 15 Nov 2014 17:20:36 GMT

Hi Henrik,

You can have an expression that looks for the missing orders. Something like

sum(if(Order='',1,0))

Just beware that the logical test might need to be different depending on how the data is read from the source. It could be 0, blank, null or something else.

If the data set is really big it will be better to do the if-statement (without sum) in the script. Then the expression only needs to be sum(MissingOrderFlag) which will be quicker than performing a if-statement for each line in the data.

See the attached example.

Best regards,

Morgan Kejerhag

Senior Business Intelligence Consultant

Drake Communication AB, Sweden

Re: Find missing order

Sat, 15 Nov 2014 19:37:04 GMT

Thank you!

I will try this, Im a bit unsure about the source if the field of customer is blank also when the order number is empty.

Best regards!

Re: Re: Find missing order

morgankejerhag — Sat, 15 Nov 2014 20:32:26 GMT

Hello again,

If you have a more traditional setup with Customers in one tab and Orders in another you can do it a little bit different. See the attached example.

Re: Find missing order

MarcoWedel — Sat, 15 Nov 2014 21:39:25 GMT

Hi,

one solution could be:

hope this helps

regards

Marco

Re: Find missing order

Sun, 16 Nov 2014 07:12:31 GMT

To get it to work with the source, first I need to see which customers are active at the specific date and then find out which of theese are missing orders.

I guess it has to be done with a join?

Re: Find missing order

morgankejerhag — Sun, 16 Nov 2014 07:22:36 GMT

It depends on the data structure. Could you attach a file or send it to me privately?

Re: Find missing order

maxgro — Sun, 16 Nov 2014 10:24:58 GMT

another one

Re: Find missing order

Sun, 16 Nov 2014 11:34:38 GMT

The source for the data is two tables, one with the fields customernumber and status (active yes or no), and the other table with the fields customernumber, ordernumber, date of the order.

What I want is to select a date and see which customers are active and have an order for this date, and which customers do not.

Re: Re: Find missing order

morgankejerhag — Sun, 16 Nov 2014 15:29:07 GMT

It is often tricky to show "what is not there".

There might be a more elegant solution to it, but attached is a version that prepares the information in the script. You could improve it by removing the synthetic key.

In the script we generate lines for each date for each customer - so now there is data selected when we click a date. For each such line we check if it should be a hit (Active, Date >= RegDate and no orderNo).