topic Re: Wildmatch 2 excel files in QlikView

Wildmatch 2 excel files

Wed, 25 Nov 2020 16:16:04 GMT

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
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.

Re: Wildmatch 2 excel files

swuehl — Tue, 17 May 2016 17:54:34 GMT

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;

Re: Wildmatch 2 excel files

MarcoWedel — Tue, 17 May 2016 21:59:50 GMT

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

Re: Wildmatch 2 excel files

Wed, 18 May 2016 09:20:05 GMT

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

Re: Wildmatch 2 excel files

Wed, 18 May 2016 10:34:20 GMT

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

Re: Wildmatch 2 excel files

swuehl — Wed, 18 May 2016 11:25:19 GMT

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

Re: Wildmatch 2 excel files

swuehl — Wed, 18 May 2016 12:12:30 GMT

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

Could you post the complete script code you are using?

Re: Wildmatch 2 excel files

Wed, 18 May 2016 12:27:50 GMT

Hi Thank,

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

Re: Wildmatch 2 excel files

swuehl — Wed, 18 May 2016 12:37:22 GMT

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

Re: Wildmatch 2 excel files

Wed, 18 May 2016 12:53:25 GMT

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

Re: Wildmatch 2 excel files

swuehl — Wed, 18 May 2016 15:31:45 GMT

Have you put a MAP table lable in front of the mapping loads?

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;

Re: Wildmatch 2 excel files

MarcoWedel — Wed, 18 May 2016 19:28:54 GMT

you're welcome.

Glad you liked it.

I think Stefan's solution should have the better performance with your larger tables, so maybe you give it another try with the additional MAP name like suggested.

regards

Marco