How to join string lists

[hugmarcel]

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

[hugmarcel]

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

[Peter_Cammaert]

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.

[Peter_Cammaert]

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

[hugmarcel]

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

[MarcoWedel]

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     
  FROM [http://community.qlik.com/servlet/JiveServlet/download/543866-110059/QlikCommunity_Thread_121005.xls...]
  (ooxml, embedded labels, table is $(vtab));
NEXT;

hope this helps

regards

Marco

[MarcoWedel]

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

[Peter_Cammaert · Accepted solution]

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

[hugmarcel]

YES

i like it, although there are other possible solutions as i.e. mentioned below.

Marcel