topic Wildcard mapping from rule set into fact table in QlikView

Wildcard mapping from rule set into fact table

jonathandienst — Wed, 27 Jul 2011 06:45:37 GMT

Hi all

I am writing a script which applies a rule set (20k rows) to a fact table (200k rows). The rule set contains a mapping of some of the attributes in the fact table to line numbers and column numbers that will be used to build a regulatory report. The simple mappings are no problem - this can be done with a join (not with a mapping table because a fact table record may map to more than one line and or column). The output is a link table which will contain a fact table key, report line, report column and value field name.

My questions concerns wild card mappings. The link table has about 3 million rows after the simple mappings but I need to eliminate rows which fail a wildcard match on one field. This field may contain blanks in the fact table. What I tried to do was this load statement:

Lookup2:
LOAD Fact.Key,
     Report.Line,
     Report.Column,
     Fact.ISC,
     Lookup.ISCMask,
     If(WildMatch(Fact.ISC, $(=Lookup.ISCMask)), 1, 0) AS Fact.Match
Resident Lookup1;

The statement works fine, except for the last line. Lookup.ISCMask contains a comma separated list of quoted match terms (for example '2.*','7.1*') and I was expecting the expansion to perform the wild card matches. Then I could use Fact.Match to eliminate those that do not match.

But it does not work - get an internal error for the expansion. It is possible to do a variable expansion here, and I have used this form of a field expansion in expressions before, but it appears not to work in script.

This post covers this problem using a variables: http://community.qlik.com/thread/32425?tstart=0 But looping over the data set using variables for the match takes far too long (several hours), although it does work.

I would welcome suggestions as to how to do this more quickly...

Jonathan

Wildcard mapping from rule set into fact table

Miguel_Angel_Baeyens — Wed, 27 Jul 2011 11:09:59 GMT

Hello Jonathan,

It seems that MapSubString() or even ApplyMap() should do what you are looking for. Where does that Lookup.ISCMask field come from? Can you use a mapping table so if your Fact.ISC field is in Lookup.ISCMask using MapSubString()?

I assume you have already tested passing the value of Lookup.ISCMask to a variable, then use this variable in the WildMatch. The expansion cannot be done in the script, it always returns "Internal Error" when it parses a "$(=" string withing a function or a variable value.

The map option, if possible, will likely be faster than the WildMatch, but not knowing your data model I don't know how to go ahead with a specific solution.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Wildcard mapping from rule set into fact table

jonathandienst — Wed, 27 Jul 2011 14:45:31 GMT

Miguel

Thanks - I am having some problem with this workstation and I will give a detailed reply later from my laptop.

Regards

Jonathan

Wildcard mapping from rule set into fact table

jonathandienst — Wed, 27 Jul 2011 19:53:17 GMT

Miguel

Thanks for looking at this -MapSubString is a possibility.

Table Lookup1 is a join of the Facttable (Fact.Key and Fact.ISC) and the Rules table (Report.Line, Report.Column,Lookup.ISCMask) so there are different match strings in Lookup.ISCMask for eachcombination of Report.Line and Report.Column). That was why I was hoping to usea field expansion, but it seems that this is not possible. I may have to buildsome sort of composite value, map that and then break it into its componentsagain.

I did try with variables, but itrequired looping over a 3 million rows building a result table of matches rowby row, which took several hours, so it tested the concept but cannot be usedon a production model.

Regards

Jonathan

Re: Wildcard mapping from rule set into fact table

rwunderlich — Thu, 28 Jul 2011 00:31:05 GMT

One approach would be to create a module function that did the test. So you could have something like

TestMask(Fact.ISC, Lookup.ISCMask) AS Fact.Match

and in your module create the TestMask function and return the correct result. It would be really nice if you could do

TestMask = ActiveDocument.Evaluate("Wildmatch(" & str & "," & mask & ")")

but that doesn't work because ActiveDocument.Evaluate is not available until after load completes.But you could do something with VBA functions, either regular expressions or the like operator.

-Rob

http://robwunderlich.com

Wildcard mapping from rule set into fact table

jonathandienst — Thu, 28 Jul 2011 10:02:16 GMT

Rob

Thanks - I hadn't thought of doing it in VBA. Will give that a shot.

Jonathan