topic Mapping with a table using wildcards in QlikView

Mapping with a table using wildcards

archire2102 — Mon, 26 Nov 2018 03:30:03 GMT

Hi,

Below is my mapping table:

Class	ID
AAASC	E002
AAAST	E004
CARs	E00301

and here is the main table:

Field1	ID
1	E002846ghdsb
3	E004fdfghrj
7	E00301fhetj

How can I apply map to the second table?

Re: Mapping with a table using wildcards

vardhancse — Mon, 26 Nov 2018 05:04:29 GMT

Hi,

Try like some thing below:

Applymap('Map',Left(ID,4)) as New

If we have a delimiter like ';' or '|', it will be very easy like using subfield (ID,';')

Re: Mapping with a table using wildcards

agni_gold — Mon, 26 Nov 2018 05:28:03 GMT

Use this :

Table1:
Load * , len(trim(ID)) as IDLenght;
LOAD * INLINE [
Class, ID
AAASC, E002
AAAST, E004
CARs, E00301
];

Source:
LOAD * INLINE [
Field1, ID
1, E002846ghdsb
3, E004fdfghrj
7, E00301fhetj
];

For i=0 to NoOfRows('Table1')-1
Let vID = Peek('ID',$(i),'Table1');
Let vClass = Peek('Class',$(i),'Table1');

Final:
Load * , if(WildMatch(ID, '*$(vID)*'),'$(vID)') as CorrectID ,'$(vClass)' as Class Resident Source;
Next;

DROP Tables Table1,Source;

Re: Mapping with a table using wildcards

jonathandienst — Mon, 26 Nov 2018 06:21:15 GMT

Rob Wunderlich created a great example of this type of wildcard mapping in his Qlikview Cookbook. Have a look here:

http://qlikviewcookbook.com/list-recipes/

Re: Mapping with a table using wildcards

archire2102 — Mon, 26 Nov 2018 22:36:07 GMT

Thanks Vardhan but the field length is not fixed so we cannot use Subfield or left. 😞

Re: Mapping with a table using wildcards

archire2102 — Mon, 26 Nov 2018 22:37:38 GMT

Thanks for the solution 🙂