topic Remove Records in script in QlikView

Remove Records in script

Fri, 09 Apr 2010 16:34:05 GMT

I have two table, one with detail records (TableDetail) and the other (Table Status) hold the records status (which can be more then one). The link between the two tables is an Order Number. If in TableStatus one of the status's for say Order Number 123 is 1 then I do not want to bring in the record from TableDetail at all in the script.

How do I do this in the script?

Thanks,
Stephen

Remove Records in script

Fri, 09 Apr 2010 17:02:23 GMT

I didn't know a way to delete records, but you can do something like this:

tmpRecordsToDelete:

LOAD OrderNumber AS OrderNumberToDelete

RESIDENT TableStatus

WHERE status = 1;

tmpTableDetail:

NOCONCATENATE

LOAD *

RESIDENT TableDetail

WHERE NOT Exists(OrderNumberToDelete, OrderNumber);

DROP TABLE tmpRecordsToDelete;

DROP TABLE TableDetail;

RENAME TABLE tmpTableDetail TO TableDetail;

Remove Records in script

Fri, 09 Apr 2010 17:16:49 GMT

You can use a where not exists(1) statement.

Remove Records in script

Fri, 09 Apr 2010 17:25:45 GMT

@Anthony It seems that the way you proupose is simpler. Could you explain it?

Remove Records in script

Fri, 09 Apr 2010 17:36:02 GMT

Actually this is the way that I would do it after reading it all again. What the script does is loads all the table from two tables intwo one. Then loads it all into the resident table where orderstatus is not 1.


Table1:
load
order number
other fields
from source.qvd
join
load
order number
order status
from source2.qvd

load
Order number
order status
resident Table1 where order status <> 1;
Drop table Table1;

Remove Records in script

Fri, 09 Apr 2010 17:42:41 GMT

Haven't tried the examples, but remember an order can have more the one status. So in the examples above are you only removing status 1. Meaning say an order has status's of 1, 2 and 3. Because the order has a 1 I do not want to see the order at all. So are the examples above going to continue to show the order because it also has status's of 2 and 3 (which I do not what)?

Stephen

Remove Records in script

Fri, 09 Apr 2010 17:51:36 GMT

Does the order have an entry for each status?

Remove Records in script

Fri, 09 Apr 2010 18:06:27 GMT

For an order in TableDetail (only one record for each order) there can be many records in TableStatus. For example, Order 123 in TableDetail can have in TableStatus: Status 1 (Order On Hold) and Status 2 (Order Not Shipped). Because the order is on hold (status 1) I do not want to see the record at all.

Hope this helps.

Stephen

Remove Records in script

Fri, 09 Apr 2010 18:09:20 GMT

Try Damián D'Onia example then. IF it doesn't work let me know and I can write one up.

Remove Records in script

Fri, 09 Apr 2010 18:10:19 GMT

Anthony, tried your suggestion and it removes status 1 for an order, but any other status's and the order number still remains in the resulting table. I want the order to not show at all (because one of the orders status's is 1).

Remove Records in script

Fri, 09 Apr 2010 18:27:14 GMT

You can find an example here: [View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.01.15.11.Posted/DeleteRows.qvw]

Remove Records in script

Fri, 09 Apr 2010 18:32:10 GMT

Yes, my example would do that. Didn't realize you had multiple order statues. IF the example posted doesn't work let me know and I can write one for you. I think his will work good though.

Remove Records in script

Fri, 09 Apr 2010 19:00:09 GMT

Damián, thanks got it working.

Stephen

Remove Records in script

Oleg_Troyansky — Sat, 10 Apr 2010 16:24:08 GMT

I guess, I'm a bit late with this suggestion, but... the easiest way to accomplish what you need is through mapping. Consider the following:


Table1:
load
 OrderID,
 ...
 Status
 ...
;
StatusMap:
mapping load
 IrderID, Status
Resident Table1
;
Table2:
load
 OrderID,
 ...
from ...
where
 ApplyMap('StatusMap', OrderID) <> 1
;

This way, the unwanted rows never make it to the database in the first place. I have a Wiki page about Mapping where I explain it in more detail...

Remove Records in script

Mon, 12 Apr 2010 15:18:08 GMT

Oleg,

Won't this approach leave the order if it also has a status of 2? I need the entire order removed, not just the status 1 part.

Stephen