Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
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?
My bad! I meant OUTER JOIN, not CROSS JOIN.
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
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