Filter same Dimensions in different tables

[SebastianRichter]

Dear experts,

I have 2 tables from two different sources recording events. Some of these events are associated, some aren't (like ID 1007 appears in table A but not in table B).

The customers in each table are the same but named differently due to different databases in use.

I cannot associate ticket id with a record id due to lack of links between both in the database but want to filter one and the same customer in both tables.

So if i select Company A US in a filter pane i want in both table only the following remain:

Table A

Ticket ID 1001 Company A US

Ticket ID 1004 Company A US

Table B

Record ID 1000000012 CompanyALPHA United States

Record ID 1000000017 CompanyALPHA United States

Here is how my tables look like:

Appreciate your help. Thanks.

[Vegar]

Have you considered concatenation? Putting both A and B into a single table.

Load TicketID,Date,Customer 

From [Table A];

Concatenate Load RecordID,Date,Customer 

From [Table B];

Using this approach you will share the dimensions of the table between the two data tables.

[SebastianRichter]

Hi Vegar,

thanks, I understand your approach. However I believe in this way "Company A US" and "CompanyALPHA United States" are still not considered as one an the same company by QS given the different spelling.

How can I change my data so both are understood as the same company.

BTW: I don't have any impact on the source tables.

Thank you in advance.

[sidhiq91]

@SebastianRichter How huge is your data set? Because I think without any common values between 2 tables it would be difficult to join the tables, even concatenation might not give you the right result.

May be Apply Map should help.

[rajjul]

If the data is not huge and limited then you can create a mapping table to match these 2 tables.

step 1: Rename Customer field in table 1 and table b as CustomerA and CustomerB

step 2: create mappingtable as below

TableAFiled	TableBField	CommonField
Company A US	Company ALPHA United States	Company AUS
Company B CN	Company BETA China	Company BCN

step 3

in table A add below filed

fieldnamenew = lookup(CustomerA, TableAFiled, CommonField ,MappingTable)

step 4

in table B add below filed

fieldnamenew = lookup(CustomerB, TableBFiled, CommonField ,MappingTable)

now based on fieldnamenew which is common in both the table you can play around. But all these are only possible only your data set of mapping table is very less data and able to configure manually.

[SebastianRichter]

Thanks guys, 

but I have round about 350 different customers to cover, new ones adding every month.

A manual maintained mapping table seems difficult and tough to maintain.

So far I understood the tables cant be associated due to different values. So my only option is to work with two selection panes, one that filters for "Company A US" and another one that filters for "Company ALPHA United" States?

[Vegar]

Harmonizing the Customer name between the two sources could be a good thing to do. This is one approach on how to do it.

MAP_Customer:
MAPPING LOAD * inline [
CustomerID, CustomerName
Company A US,CompanyALPHA United States
Company B CN, CompanyBETA China
Company C FR, CompanyCHARLlE France
Ccompany A US, CompanyALFA United States
Company D CN, CompanyDELTA China
Company E UK,CompanyECHO United Kingdom
Company F DE, CompanyFoxtrot Germany
];

//TABLE A
[Table]:
LOAD 
[Ticket ID] as ID,
'Ticket' as [Transaction type]	,
Date,		
applymap('MAP_Customer',Customer, 'Missing in mapping table') as Customer

inline [
Ticket ID,	Date,		Customer
1001,		01.01.2023,	Company A US
1002,		02.01.2023,	Company B CN
1004,		03.01.2023,	Company C FR
1004,		04.01.2023,	Company A US
1005,		05.01.2023,	Company D CN
1006,		05.01.2023,	Company E UK
1007,		06.01.2023,	Company F DE
];

//TABLE B
Concatenate (Table)
LOAD 
	[Record ID] as ID,	
	'Record' as [Transaction type]	,
	Date,		
	Customer
inline [
Record ID,	Date,		Customer
1300000012,	01.01.2023,	CompanyALPHA United States
1300000013,	02.01.2023,	CompanyBETA China
1300000015,	03.01.2023,	CompanyCHARLlE France
1300000017,	04.01.2023,	CompanyALPHA United States
1300000018,	05.01.2023,	CompanyDELTA China
1300000019,	05.01.2023,	CompanyECHO United Kingdom
];

[marcus_sommer]

Beside harmonizing the customer-information per manual mapping like suggested from @Vegar you may also add some generic approaches by cleaning and preparing the values by various string-operations like trim(), upper/lower() and so on to minimize the manual maintaining efforts.

[Parthiban]

Try this expression

=left(CustomerName,7)&' '&mid(CustomerName,8,1)&' '&
if(len(left(SubField(CustomerName,' ',2),1)&''&left(SubField(CustomerName,' ',3),1))=1,left(SubField(CustomerName,' ',2),2),
left(SubField(CustomerName,' ',2),1)&''&left(SubField(CustomerName,' ',3),1))

 This expression will fails when the state name become one like china, germany (here this expression will take ch,ge) but u have China as CN. 

And also I will check any other possibilites....