topic Re: How to exclude double data from different QVD? in QlikView

How to exclude double data from different QVD?

Fri, 11 Nov 2016 18:04:37 GMT

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);

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

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

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!

Re: How to exclude double data from different QVD?

johnw — Fri, 11 Nov 2016 18:43:18 GMT

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
;

Re: How to exclude double data from different QVD?

maxgro — Fri, 11 Nov 2016 18:46:31 GMT

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?

its_anandrjs — Fri, 11 Nov 2016 19:05:24 GMT

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

Re: How to exclude double data from different QVD?

Fri, 18 Nov 2016 13:14:13 GMT

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?

rubenmarin — Fri, 18 Nov 2016 13:22:27 GMT

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.

Re: How to exclude double data from different QVD?

Tue, 22 Nov 2016 13:00:50 GMT

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:

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;

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:

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

Thanks.

Re: How to exclude double data from different QVD?

johnw — Mon, 28 Nov 2016 20:30:17 GMT

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];