Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have 2 tables A and B with
A: Field1
B: Field1
Field1 contains a number list as value, such as '1:2:3:4:5' or '2:4:6' etc.
Joins should match if number lists contain identical numbers and have identical length. E.g.:
A.Field1 = '1:2:3:4:5' joins with B.Field1 = '1:3;2:4:5' but not with '2:4:6' or '1:3;2:4:5:5'
Any help appreciated!
Thx - Marcel
And this is even shorter. I think you'll like it:
// Sort field theTable.theField with theDelimiter, create new field theFieldSorted
SUB SortField ( theTable, theField, theDelimiter, theFieldSorted );
LEFT JOIN ($(theTable))
LOAD $(theField), CONCAT(ELEMENTS,'$(theDelimiter)') AS $(theFieldSorted) GROUP BY $(theField);
LOAD $(theField), SUBFIELD($(theField),'$(theDelimiter)') AS ELEMENTS
RESIDENT $(theTable);
END SUB
If you're satisfied, please close the thread. Thanks
I found a solution, such as:
1.) Table C: Contains outer Join of Tables A and B (with FIELD1 renamed to FIELD2) .
2.) Reduce Table C to records fullfilling condition:
LEN(PURGECHAR(FIELD1,FIELD2)) + LEN(PURGECHAR(FIELD2,FIELD1)) = 0 AND LEN(FIELD1) = LEN(FIELD2);
However, I hope there is a better solution, as OUTER Joining is not nice.
Thx - Marcel
I think you found the simplest solution (although it's memory-hungry)
The other solution I can think of involves reordering key values (using substring() and concat()) to a common (ascending) format, but that means more code.
Small example of an alternative approach. Note that I used semicolons instead of colons. QlikView may mess with your key values if it thinks that they can be treated as time specifications (especially the shorter ones).
Good luck,
Peter
Hi Peter
thank you for this solution!
I do not like the outer join method, and I always try to keep coding as small and concise as possible. I therefore created a sub
which appends a new sorted field theFieldSorted to the table. Joining over sorted fields then gives correct matches too. See
// Sort field theTable.theField with theDelimiter, create new field theFieldSorted
SUB SortField ( theTable, theField, theDelimiter, theFieldSorted );
FIELD_ELEMENTS:
LOAD $(theField), SUBFIELD($(theField),'$(theDelimiter)') AS ELEMENTS
RESIDENT $(theTable);
LEFT JOIN ($(theTable))
LOAD $(theField), CONCAT(ELEMENTS,'$(theDelimiter)') AS $(theFieldSorted)
RESIDENT FIELD_ELEMENTS
GROUP BY $(theField);
DROP TABLE FIELD_ELEMENTS;
END SUB
Thx - Marcel
Hi Marcel,
one possible solution could be also:
QUALIFY 'Field*';
FOR Each vtab in 'A', 'B'
$(vtab):
LOAD AutoNumber(Concat(F1, ':')) as %Field1ID,
Field1,
Field2,
Field3
Group By Field1, Field2, Field3, RecNo;
LOAD *,
SubField(Replace(Field1, ';', ':'), ':') as F1,
RecNo() as RecNo
(ooxml, embedded labels, table is $(vtab));
NEXT;
hope this helps
regards
Marco
Replacing
LOAD AutoNumber(Concat(F1, ':')) as %Field1ID,
with
LOAD Concat(F1, ':') as %Field1ID,
in my previously mentioned solution delivers a normalized key value:
regards
Marco
And this is even shorter. I think you'll like it:
// Sort field theTable.theField with theDelimiter, create new field theFieldSorted
SUB SortField ( theTable, theField, theDelimiter, theFieldSorted );
LEFT JOIN ($(theTable))
LOAD $(theField), CONCAT(ELEMENTS,'$(theDelimiter)') AS $(theFieldSorted) GROUP BY $(theField);
LOAD $(theField), SUBFIELD($(theField),'$(theDelimiter)') AS ELEMENTS
RESIDENT $(theTable);
END SUB
If you're satisfied, please close the thread. Thanks
YES
i like it, although there are other possible solutions as i.e. mentioned below.
Marcel