Filter two fields with same data in the same table

[wospivus]

I have a table that contains two fields; DeliveredBy and PreparedBy. Both fields contain names of employees. I want to be able to have a filter where I select the name of an employee and the filter filters both DeliveredBy and PreparedBy. How do you make the best way to get two fields filtered by one select?

[Lisa_P]

This is best done in the data load.

If you have this data:
    Load Date,
              DeliveredBy as Name,
              'DeliveredBy' as Function
           from ....

   Load Date,
             PreparedBy as Name,
              'PreparedBy' as Function
           from ....

Because the field names are the same, the tables will concatenate and you have a new field to use to separate the Function types.

[wospivus]

You will excuse my ignorance but I am new to Qlik. I'm not following. When I make the suggested solution and load this after I have loaded the other fields in the table, I get a link to synthetic keys that show the data incorrectly. I can't load it together with the other fields in the table because field names must be unique.

[Rodj]

What @Lisa_P is trying to lead you to is a concatenation of your original table through loading it twice with the same aliased column names. Behind the scenes the Qlik engine then sees this as one table but would effectively give you the single link (under the field name Function) to both your PreparedBy and DeliveredBy columns.

If you load a table twice with the same column names the Qlik engine will automatically concatenate them (think in terms of the Qlik engine treating each column individually, wherever it sees the same column name it treats this as a single column/index everywhere in the data). You've mentioned that you now get synthetic keys which suggests you may have loaded the data twice but with at least one different column name, thus the tables have not been concatenated. You can concatenate tables with different column names as well, simply by using the "concatenate" load prefix, this is called a forced concatenation.

If this still isn't making sense perhaps give us a sample of your data or a screenshot of your data model and we should be able to decipher it.

Cheers,

Rod

[wospivus]

You are welcome to show me in the code how to do. Here are my table

[Orders]:

LOAD

[OrderDate],

[OrderOrderer],

[OrderOrdererPhone],

[OrderNo] AS _KeyOrderNumber, //KEY

[OrderNo],

[OrderDeliveryDate],

[OrderDeliveredBy],

[OrderPreparedBy],

[OrderCompany],

[OrderComment],

[OrderRecipientCustomer],

[OrderLink],

[OrderPlacement],

[OrderPreliminaryDeliveryDate],

[OrderStatus],

[OrderCompanyNo] AS _KeyCustomerNumber, // KEY,

[OrderTicketNo]

 FROM [lib://data/Orders.xlsx]

(ooxml, embedded labels, table is Orders);

[Rodj]

Ok, there's at least a couple of ways you can do this, but I'll stick with the most simple and hope it works without understanding the broader context of your model. After loading your Orders table try this:

Function:

Load

    OrderNo,

    OrderPreparedBy as Function

resident Orders;

// concatenate is implied as fields are the same in our new Function table

Load 

    OrderNo,

    OrderDeliveredBy as Function

resident Orders;

This will create a Function table and an Function column that you can now use to filter on a single field to those records associated with the same employee, regardless of whether they prepared or delivered the order.

[wospivus]

It worked to create a field for filtering, but the filtering in the diagram is after all incorrect. I only want to see the person I selected in the filter. As you can see in diagram 2, three other people are also shown.