topic Splitting a field depending on a field in a second table in QlikView

Splitting a field depending on a field in a second table

Wed, 28 Sep 2011 11:03:34 GMT

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.

Re: Splitting a field depending on a field in a second table

Wed, 28 Sep 2011 11:11:49 GMT

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

Re: Splitting a field depending on a field in a second table

gussfish — Wed, 28 Sep 2011 11:18:10 GMT

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

Splitting a field depending on a field in a second table

Wed, 28 Sep 2011 12:01:44 GMT

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?

Splitting a field depending on a field in a second table

gussfish — Wed, 28 Sep 2011 23:39:24 GMT

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

Splitting a field depending on a field in a second table

rwunderlich — Thu, 29 Sep 2011 00:51:32 GMT

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

Splitting a field depending on a field in a second table

Wed, 05 Oct 2011 10:17:21 GMT

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