Check if a value already exists in another field in the same table

[cheryl]

Hi,

I want to check if the value in the field ProjectID (in the current record) already exists in another field p.ParentID in the same table. I load all the data from a qvd file.

Project:
LOAD Distinct
p.billable,
p.ParentID,
p.ProjectID as ProjectID,
if(p.billable='True' and exists(p.ParentID,p.ProjectID),'TRUE','FALSE') as Is_parent,
FROM [lib://QVD-xxx.qvd]
(qvd);

However I got results like this (graph as an example):

Some of them has the correct results (Is_parent is TRUE when ProjectID is existing in ParentID, see yellow highlighted).

Some of them gave wrong results (Even when ProjectID is existing in ParentID, Is_parent is FALSE, see green highlighted)

This is very weird to me. Does anybody know why this happened? 

Or is there any other solution to my problem?

Thanks very much!

Cheryl

[tresesco]

Hello Cheryl,

Are you considering the 'load order' while showing this data in table and trying to  comprehend? 

[cheryl]

Hi Tresesco,

I am not quite clear what you mean here. I used the above-mentioned script and I would like to search for all IDs in ParentID field regardless of load order. When showing the data in the table above, I just use another field to put them together.

Thanks,

Cheryl

[tresesco]

Let's take up the first row from your table. As per your analysis - it is 'wrong' because the value 'P-61201' is present in the parent field. However, this is very much possible that this particular record is being loaded before any of the other records that has this value in p.ParentID field. That is means exists() would check if a value has been loaded so far or not. Therefore, if a value in the p.ProjectID comes Nth record, and it appears in (N+x)th row in p.ParentID you get false for that record; simply because for the Nth record that value is still not there in target column.

Not sure, if I could make this a bit clearer now or not.

[cheryl]

Hi Tresesco,

Thanks for your explanation! I understand now that it is caused by the load order.

However do you have any idea how to achieve my goal: search whether ProjectID exists in p.ParentID regardless of the load order?

Thanks!

Cheryl

[tresesco · Accepted solution]

Load the parent field before this load separately, something like:

AllParents:
LOAD  p.ParentID as ParentAll FROM [lib://QVD-xxx.qvd] (qvd);

Project:
LOAD Distinct
p.billable,
p.ParentID,
p.ProjectID as ProjectID,
if(p.billable='True' and exists(ParentAll,p.ProjectID),'TRUE','FALSE') as Is_parent,
FROM [lib://QVD-xxx.qvd]
(qvd);

[cheryl]

This solved my problem! Thanks very much!