topic Re: Extract only Open records in App Development

Extract only Open records

Anku — Mon, 04 Oct 2021 06:15:04 GMT

Hi all,

I am working with QVD file consists Orders data. Please refer below:

I am trying to create a table having Open orders only. For example, in above scenario only, 45632.

I can't use Where clause as it will only exclude the one record for closed orders only. Kindly suggest.

Re: Extract only Open records

hic — Mon, 04 Oct 2021 06:42:54 GMT

The following could perhaps be a solution:

TempTable:
Load Order_ID as ClosedOrders From SourceFile.qvd (qvd) Where Status='Closed';

Data:
Load * From SourceFile.qvd (qvd) Where not Exists(ClosedOrders,Order_ID);

Drop Table TempTable;

Re: Extract only Open records

Anku — Mon, 04 Oct 2021 09:00:17 GMT

Thanks Henric,

2 queries, just to enhance my understanding:

>For table data, can we use resident load ( which one is more preferable?)

>Condition we have used : where not exists, can we use the fields in this clause even they belongs to another table?

Thanks:)

Re: Extract only Open records

hic — Mon, 04 Oct 2021 09:07:31 GMT

Yes, you can use resident load. But if you work with qvd files, loading from a file is probably just as fast, so there is no real advantage using resident load.

Yes, the first parameter of the Exists() can point to a field from a previously loaded table. The second parameter must however be from the current table.