topic Re: How to join string lists in QlikView

How to join string lists

hugmarcel — Thu, 05 Jun 2014 15:03:23 GMT

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

Re: How to join string lists

hugmarcel — Thu, 05 Jun 2014 15:49:26 GMT

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

Re: How to join string lists

Peter_Cammaert — Thu, 05 Jun 2014 16:40:26 GMT

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.

Re: Re: How to join string lists

Peter_Cammaert — Fri, 06 Jun 2014 13:31:07 GMT

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

Re: Re: How to join string lists

hugmarcel — Fri, 06 Jun 2014 14:41:26 GMT

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

Re: How to join string lists

MarcoWedel — Fri, 06 Jun 2014 23:44:01 GMT

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.xlsx]
  (ooxml, embedded labels, table is $(vtab));
NEXT;

hope this helps

regards

Marco

Re: Re: How to join string lists

MarcoWedel — Sat, 07 Jun 2014 00:14:10 GMT

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

Re: How to join string lists

Peter_Cammaert — Tue, 10 Jun 2014 08:59:06 GMT

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

Re: How to join string lists

hugmarcel — Tue, 24 Jun 2014 14:22:52 GMT

YES

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

Marcel