Splitting a field depending on a field in a second table

[#1]

I have two tables

One with the make  of cars (eg Volkswagen, Alfa Romeo) and the other with Models in the form of Volkswagen Polo, Alfa Romeo Guilietta.

I want a key on 'Make', but don't know how to split the make and model in the second table.

Using SUBFIELD works, but only for one word Makes.

Has someone an idea.

[#2]

try using len and right functions. .

right(Model,(len(Model)-len(Make)))

len(Model)-len(Make) should give you the number of characters you need to keep after the Make. If there is an extra space at the begining change it to

(len(Model)-len(Make))+1

[gussfish]

Well, it's a bit messy, but you could do some logic to find which value

in the 1st table matches each given value in the 2nd table.

The concept is to

1. CROSS JOIN the AllMakes table onto the AllModels table.  So, now you

have AllCombinations, containing every combination of Make and Model

2. JOIN to that table a flag that specifies when the Model begins with

the Make i.e. something like

JOIN (AllCombinations)

LOAD

    make,

    model,

    (left(make,len(model))=model) AS IsThisMake

Resident AllCombinations;

3. Do a KEEP to get rid of all records except those for which IsThisMake

is true

Angus

[#4]

Ok. this is sort of what I had in mind, but I cannot find the syntax for the Cross join

Can you help again, please?

[gussfish]

My bad!  I meant OUTER JOIN, not CROSS JOIN.

[rwunderlich]

I would use wildcard mapping as shown in the QV Cookbook. The mapping example in the cookbook works fine, but there is a newer simplified version attached to this thread

http://community.qlik.com/message/146315

Just load up your wildmatch table with

make & '*', make

Rob

[#7 · Accepted solution]

Thank you to all who tried helping.

With help from  a colleague we solved it as follows:

LET NumberOfRows = NoOfRows('MAKE');
FOR i = 0 to $(NumberOfRows)-1
LET C= peek('Make', $(i) , 'MAKE');
MODEL:
LOAD
   @2 AS ModelName,
  Len(@2) AS ModelLength,
  '$(C)' AS Make,
  Len('$(C)') AS MakeLength,
  Right(@2, Len(@2)-(Len('$(C)')+1)) AS Model
FROM
  [http://www.autozine.nl/enquete_verkoop.html]
  (html, codepage is 1252, no labels, table is @1)
  where @2 Like '*$(C)*' ;
NEXT