Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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!
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!
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.