Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

How to exclude double data from different QVD?

Hi,

I need to exclude the orderid where the plan_date and OrderDate matches. The only problem is that the plan_date and the OrderDate data are in different qvd. Now I joined these two together like this:

Datums:

LOAD orderid,

          plan_date

          From [DatabaseA.qvd]

(qvd);

Inner Join (Datums)

         LOAD orderid,

                   OrderDate

                   FROM [DatabaseB.qvd]

(qvd);

orderidplan_dateOrderDate
131-1-20154-1-2016
228-11-20154-1-2016
316-12-20154-1-2016
418-12-20154-1-2016
521-12-20154-1-2016
621-12-20154-1-2016
721-12-20154-1-2016
82-1-20164-1-2016
94-1-20164-1-2016
104-1-20164-1-2016

                                                          

131-1-20154-1-2016
228-11-20154-1-2016
316-12-20154-1-2016
418-12-20154-1-2016
521-12-20154-1-2016
621-12-20154-1-2016
721-12-20154-1-2016
82-1-20164-1-2016
94-1-20164-1-2016
104-1-20164-1-2016

The last two orderid's have the same plan_date and OrderDate. I want to exclude these two orderid's, but I have no idea how I have to do that.

Could anyone help me?

Thanks!

7 Replies
MVP
MVP

Re: How to exclude double data from different QVD?

If orderid is a unique key (I'm assuming you accidentally duplicated all the rows in the post):

INNER JOIN (Datums)
LOAD orderid
RESIDENT Datums
WHERE plan_date <> OrderDate
;

MVP
MVP

Re: How to exclude double data from different QVD?

after the join you ca n do a resident load of your table excluding the rows with the same date

noconcatenate

load

     *

resident

     Datums

where

     not (plan_date = OrderDate)

     ;

Re: How to exclude double data from different QVD?

This way also you can try

LOAD * Inline

[

orderid, plan_date, OrderDate

1, 31-1-2015, 4-1-2016

2, 28-11-2015, 4-1-2016

3, 16-12-2015, 4-1-2016

4, 18-12-2015, 4-1-2016

5, 21-12-2015, 4-1-2016

6, 21-12-2015, 4-1-2016

7, 21-12-2015, 4-1-2016

8, 2-1-2016, 4-1-2016

9, 4-1-2016, 4-1-2016

10, 4-1-2016, 4-1-2016

];

NoConcatenate

LOAD

*

Resident Data

Where (plan_date <> OrderDate);

DROP Table Data;

Regards,

Anand

Not applicable

Re: How to exclude double data from different QVD?

Thank you, this works. The only problem now is that these data are hidden, but I want them to be completely removed. Do you have a solution for that?

Re: How to exclude double data from different QVD?

Hi Stefan, another option:

Datums:

LOAD orderid,

          plan_date,

          orderid & '#' & plan_date as check

          From [DatabaseA.qvd]

(qvd);

Inner Join (Datums)

        LOAD orderid,

                  OrderDate

                  FROM [DatabaseB.qvd] Where not exists('check', orderid & '#' & OrderDate)

(qvd);

DROP Field check;

I didn't tested but the inner join should also remove the rows from the first table.

Not applicable

Re: How to exclude double data from different QVD?

I am sorry guys, I think i should have explained it better, so here is attempt number two.

Here is my original qvw with 4 different QVD's:

table A.png

What I want is to remove the orderid's out of my qvw where the plan_date and the OrderDate have the same date. You can see these two things are in two different QVD's: OrderDate is in 'Planning' and plan_date is in 'Afhaalwagen'.

I have made a new table where I put the orderid, plan_date and OrderDate in one table, but now I get synthetic keys.

Datums:

LOAD orderid,

          plan_date

FROM $(vPath)afhaalwagennummer.qvd (qvd);

inner join

Load orderid,

        OrderDate

FROM $(vPath)planning.qvd (qvd);

NewTable:

NoConcatenate

LOAD orderid,

     plan_date,

     OrderDate

RESIDENT Datums

WHERE not (plan_date = OrderDate);

Drop Table Datums;

Table B.png

The problem is, in the new table the orderid with the same plan_date and OrderDate is hidden, but these orderid's are still in the whole qvw.

What I actually want is the following:

Table C.png

Am I thinking it totally wrong? Because I have the feeling I am close, but I can't get it clear.

Thanks.

MVP
MVP

Re: How to exclude double data from different QVD?

Is orderid a unique key to all three tables? If so, join Afhaalwagen and Indelingen onto the Planning table, then use my previous inner join suggestion. But I suspect that each has a different unique key, even if they all share orderid, and so you'd want to keep them separate. If so, perhaps something like this:

[Delete Orders]:
LOAD
orderid as [Delete Order]
,plan_date
FROM $(vPath)afhaalwagennummer.qvd (qvd);

INNER JOIN ([Delete Orders])
LOAD
orderid as [Delete Order]
,OrderDate
FROM $(vPath)planning.qvd (qvd);

INNER JOIN ([Delete Order])
LOAD *
WHERE plan_date = OrderDate;

INNER JOIN (Planning)
LOAD your key fields
RESIDENT Planning
WHERE not exists([Delete Order],orderid);

INNER JOIN (Afhaalwagen)
LOAD your key fields
RESIDENT Afhaalwagen
WHERE not exists([Delete Order],orderid);

INNER JOIN (Indelingen)
LOAD your key fields
RESIDENT Indelingen
WHERE not exists([Delete Order],orderid);

DROP TABLE [Delete Orders];

Community Browser