How do I load a table by removing line associated to datas part of a second table

[Tadyvy]

Dear all,

I'm new to Qlik sense and facing an issue with a specific table I try to load.

Indeed I have two tables (= see example below) :

[COST CENTER]:

Cost center ID	Budget owner ID
2K29	0124528Z
2028
25A3	0147889A
4587	7305485

In this first table, I find all cost centers and the budget owner ID associated for each of them.

For some cost center there is no budget owner determined. The field Budget owner ID stay blank.

[USER ID]

User ID

0124528Z

0147889A

0879547X

7354789

In a second table, I have a list of User ID.

I want to display the first table by removing the line on which I can find a User ID part of [User ID] table.

I want also to keep the line on which there is no budget owner ID determined.

So, if we keep example below, at the end I want a table loaded like below:

[COST CENTER}:

Cost center	Budget owner ID
2028
4587	7305485

Indeed, 

0124528Z is part of [User ID] table so I want the line removed

0147889A is part of [User ID] table so I want the line removed

7305485 is not part of  [User ID] table so I want to keep and display the line

For the cost center 2028, there is no Budget owner ID so I want to keep the line.

At the end, there is only the two lines above displayed.

How in data load editor, I can handle this?

Looking forward to an answer.

Best regards

David

[rwunderlich · Accepted solution]

Load the Userid table first. Then load the Cost Center table with a where clause like:

Where not exists([User ID], [Budget owner ID])

-Rob

[Tadyvy]

Thank you rob, it has been very helpful ! 🙂

I have a last request to finalize my table.

When I display my table, I still have some lines with "Budget owner ID" with no "Cost center ID" associated.

It concerns "Budget owner ID" part of [User ID] table.

To summarize quickly if I keep the example below, I have the result below:

Cost center	Budget owner ID
2028
4587	7305485
-	0124528Z
-	0147889A

How can I remove the two last lines too from the final table, indeed these two are part of [USER ID] table ?

I tried this:

Where not exists([User ID], [Budget owner ID]) and not isnull([Cost center ID])

Unfortunately I still find some lines without any "Cost center ID" and "Budget owner ID" associated. 

Have you got an idea?

Thank you in advance

David

[rwunderlich]

Can you post the script that creates the COST CENTER table?

-Rob

[Tadyvy]

Hello Rob,

I solved my issue by unticking "include null values" from cost center datas from my table.

Thank you for your help.

Best regards

David