Wildmatch 2 excel files

[#1]

Hi,

I am new to qlikview, but am experienced in other programming languages VBA, SQL, C and I am just needing some help with a problem that I have.

I have 2 excel sheets sheet 1 contains a list of customers and has 5 fields that I need to look at ("address line 1", "address line 2", "address line 3", "address line 4", "address line 5")

Sheet 2 then contains a list of "Countries", there "Demonym" and there "status"e.g

Address 1	Address 2	Address 3	Address 4	Address 5
15 The walk	Yorkshire	England
10 Scottish	Scotland
10 The walk Germany		France

Country	Demonym	Status
England	English	Good
Scottish	Scotland	Bad
Germany	German	Indifferent

What I need to do is find if any of the countries or there Demonym appears in address line 1-5, and then match that to there status.

so for the above I would expect it to find England in address line 3 and output the status good, I would expect it to find Scottish in address line 1 and Scotland in address line 2 and output the status bad. And i would expect it to find Germany in address line 1 and output the Status Indifferent.

Couple of Issues been that I do not produce either of the excel sheets so there format is what I am stuck with and the amount of countries on sheet 2 is completely random and can be updated at any point. Finally I need to have a full register of sheet 1 without any changes made to it loaded into qlikview so I am unable to do the checking through the load function.

[swuehl]

Why can't you do it in the LOAD script?

Table1:

LOAD  * INLINE [

Address 1, Address 2, Address 3, Address 4, Address 5

15, The walk, Yorkshire, England

10, Scottish, Scotland,

10, The walk Germany, ,France

];

Table2:

LOAD * INLINE [

Country, Demonym, Status

England, English, Good

Scottish, Scotland, Bad

Germany, German, Indifferent

];

MAP:

MAPPING LOAD Country as F1, '>Begin>'&Status&'<End<' as F2

Resident Table2;

MAPPING LOAD Demonym as F1, '>Begin>'&Status&'<End<' as F2

Resident Table2;

RESULT:

LOAD *, Textbetween(MapSubString('MAP', SearchField), '>Begin>','<End<') as Status;

LOAD *, "Address 1" & "Address 2" & "Address 3" & "Address 4" & "Address 5" as SearchField

Resident Table1;

DROP TABLE Table1, Table2;

DROP FIELD SearchField;

[MarcoWedel · Accepted solution]

Hi,

another solution might be:

tabCustomers:
LOAD RecNo() as %CustKey, *
FROM [https://community.qlik.com/thread/217156] (html, codepage is 1252, embedded labels, table is @1);

tabCountries:
LOAD RecNo() as %CountrKey, *
FROM [https://community.qlik.com/thread/217156] (html, codepage is 1252, embedded labels, table is @2);

tabTemp1:
CrossTable (temp1, Address)
LOAD * Resident tabCustomers;

tabTemp2:
CrossTable (temp2, CntrDmnym)
LOAD * Resident tabCountries;

tabTemp3:
LOAD Distinct %CustKey, Address Resident tabTemp1;
Join
LOAD Distinct %CountrKey, CntrDmnym Resident tabTemp2;

tabLink:
LOAD Distinct %CustKey,%CountrKey
Resident tabTemp3
Where Address like '*'&CntrDmnym&'*';

DROP Tables tabTemp1, tabTemp2, tabTemp3;

Depending on your data the cartesian product might decrease the performance of this solution though.

hope this helps

regards

Marco

[#4]

Hi When I try to load this I get the following error

Error in expression:

MapSubString function takes a constant map as first parameter

LOAD *, Textbetween(MapSubString('MAP', SearchField), '>Begin>','<End<') as Status

[#5]

Hi,

With regards to this how would I look to change it if the files are stored locally rather then retrieving them from the website as the actual file has more records, and more fields

[swuehl]

In both suggested solutions, the first two tables just load your data, so just replace the first two LOAD statements with appropriate statements to LOAD from your source.

You would need to adapt your load statement code a bit, to

a) create the Keys in Marco's sample

b) adapt the CROSSTABLE LOAD prefix if needed

c) Add more fields to the concatenated search field in my sample

d) Create the mapping table from all fields to create all key / value pairs

[swuehl]

And have you created the MAP table as shown in my sample?

Could you post the complete script code you are using?

[#8]

Hi Thank,

Both very good answers here I went with Marco's in the end as I found it the simplest to understand

[swuehl]

My solution is not working for you at all, or you find Marco's solution just superior in your setting?

[#10]

Hi i found Marcos example a little easier to get used to I copied yours exact as bellow the X in the file names are just because the names are quite sensitive to what is been done.

LOAD [Address 1],

     [Address 2],

     [Address 3],

     [Address 4],

     [Address 5]

FROM

(biff, embedded labels, table is Table1$);

LOAD Country,

     Demonym,

     Status

FROM

(ooxml, embedded labels, table is Table2);

MAPPING LOAD Country as F1, '>Begin>'&Status&'<End<' as F2

Resident Table2;

MAPPING LOAD Demonym as F1, '>Begin>'&Status&'<End<' as F2

Resident Table2;

LOAD *, Textbetween(MapSubString('MAP', SearchField), '>Begin>','<End<') as Status;

LOAD *, "Address 1" & "Address 2" & "Address 3" & "Address 4" & "Address 5" as SearchField

Resident Table1$;

DROP TABLE Table1$, Table2;

DROP FIELD SearchField;

Its probably something simple that I have missed