Joining Tables with flawed multiple IDs/key values

[#1]

I have 3 tables that I want to left join where one is a table that has the IDs of all the rows that I want in the output and the other two are crosstables with data. The problem is that I have multiple ID fields some of which don't match for some reason.

A small excerpt would be:

mapping:

ID	ID2	ID3	...
1	xzz	456	...
2		567	...

datatable1

ID	ID2	ID3	Data...
1	xyz	456	...
2	xya	567	...

datatable2

ID	ID2	ID3	Data...
1	xyz	456	...
3	xyu	547	...

Now I know I could just correct the mapping source, but to be honest the source files are a mess and I am not supposed to mess with them. Now what I have so far is:

mapping:

LOAD

ID,

[ID2],

[ID3],

...

FROM ...

temp:

LOAD

[ID2],

[ID3],

DATA1,

DATA2,

...

FROM datatable1_source...

CONCATENATE(temp)

LOAD

[ID2],

[ID3],

DATA1,

DATA2,

...

FROM datatable2_source...

WHERE NOT EXISTS([ID2])

crosstab:

CrossTable(A, Data, 2)

LOAD * Resident temp;
Drop Table temp;

LEFT JOIN(mapping)

LOAD

[ID2],

[ID3],

Data

Resident crosstab;

drop table crosstab;

Now this seems to work for all the fields that don't have a missing or wrong id in mapping. What I would want is something like

If ID2 doesn't have matches, look for ID3 and overwrite ID2. Or maybe only declare one ID at a time in mapping for a single key value and join them afterwards? Can't think of an easier way right now, help would be appreciated. Thanks!

[Oleg_Troyansky · Accepted solution]

Hi Darren,

I think the key question here is what are the logical relationships between these IDs? Do they represent different keys to the same data entity - for example, Member ID vs. SSN#, both leading to the same person? Or, do they represent different data entities, such as Student ID and Class ID, for example?

If you can lay out the logical rules of how to look up and substitute missing keys, then you could use mapping to cleans your data in QlikView, before joining the tables.

hope this approach helps,

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy and Austin, TX!

[#3]

Hello Oleg,

Thanks for your answer. They represent keys to the same datapoint, but some of them have not been updated or kept clean. I somehow got it to work using two mapping tables with 1 ID each, left joining the crosstab with both and the concatenated them.